November 15, 2011 at 3:56 pm
I want to pass selected Ids to stored procedure to get details from client table. If I dont pass selected Ids (table) then I want all clients from client table
Here is the code for my SP.
CREATE PROCEDURE [dbo].[usp_TestSP]
@tblClientIds dbo.tblTypeIds = null readonly
AS
BEGIN
SET NOCOUNT ON;
SELECT * from Client where @tblClientIds is null or ClientId in (Select ID from @tblClientIds)
END
But SP gives me error in where condition "must declare the scalar variable '@tblClientIds'."
Can I not pass null to table variable? or how do I solve this problem
November 15, 2011 at 8:56 pm
Try This.
CREATE PROCEDURE [dbo].[usp_TestSP]
@tblClientIds dbo.tblTypeIds = null readonly
AS
BEGIN
SET NOCOUNT ON;
Declare @TEMPID varchar(10)
Select @TEMPID=ID from @tblClientIds
SELECT * from Client where ClientId in (CASE WHEN @TEMPID is NULL THEN ClientId ELSE (Select ID from @tblClientIds)END)
END
🙂
November 15, 2011 at 9:07 pm
I haven't really used table valued parameters much but I'll take a stab. First, if I try to set the default of the parameter to null I get an error. I believe that table valued parameters are optional by default and if not passed then the table is still there but contains no rows.
So here's an idea:
CREATE procedure dbo.usp_Test
@clientids tblTypeIds READONLY
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM @clientids) = 0
SELECT * FROM HumanResources.Employee
ELSE
SELECT * FROM HumanResources.Employee where EmployeeID IN (SELECT ID FROM @clientids)
END
This wouldn't work if you actually do want to pass in an empty table and get back no results. But it does give all rows if you simply don't pass the table to the proc. There may be a better way to do the test.
You'd probably want to even make it use a join instead of the IN
ALTER procedure dbo.usp_Test
@clientids tblTypeIds READONLY
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM @clientids) = 0
SELECT * FROM HumanResources.Employee
ELSE
SELECT * FROM HumanResources.Employee E
JOIN @clientids C ON
E.EmployeeID = C.ID
END
November 15, 2011 at 9:12 pm
Bipinkumar. I don't think that will work if you pass in a table with more than 1 row unless I set it up wrong.
Cliff
November 15, 2011 at 10:10 pm
Try This.
CREATE PROCEDURE [dbo].[usp_TestSP]
@tblClientIds dbo.tblTypeIds = null readonly
AS
BEGIN
SET NOCOUNT ON;
Declare @TEMPID int
Select @TEMPID=COUNT(*) from @tblClientIds
IF @TEMPID=0
BEGIN
SELECT * from Client
END
ELSE
BEGIN
SELECT * from Client where ClientId in (Select ID from @tblClientIds)
END
November 15, 2011 at 11:48 pm
@tblClientIds is null
This statement is wrong. You declared it as Table variable and trying to use as Scalar variable.
In fact I am not convinced on the approach itself. Why don't you create a valid table and insert a NULL value in it. Fetch NULL in your query if you want (or filter it with WHERE clause).
November 16, 2011 at 8:39 am
Thanks all.
I thought of workaround like counting # of records and use If Else condition. But I wanted to keep Table variable optional, but looks like that is not a case in this situation.
I will have to go back with my original idea and as suggested in this post of counting records and use If..Else
I am just curious, if table variable is optional and not passed, how do someone find it?
November 16, 2011 at 8:48 am
I am just curious, if table variable is optional and not passed, how do someone find it?
I can't test it but it should behave like Empty Table because Table Type you have already created (Outside the scope of SP).
November 16, 2011 at 1:13 pm
corder (11/15/2011)
You'd probably want to even make it use a join instead of the INCliff
I tried JOIN but gives me same error, can't join with table variable. That is strange;-)
November 16, 2011 at 1:55 pm
mak101 (11/16/2011)
corder (11/15/2011)
You'd probably want to even make it use a join instead of the INCliff
I tried JOIN but gives me same error, can't join with table variable. That is strange;-)
Huh!, what code did you try???
November 17, 2011 at 10:14 am
SELECT * from Client Join @tblClientIds ON @tblClientIds.Id = Client.ClientId
November 17, 2011 at 10:17 am
mak101 (11/17/2011)
SELECT * from Client Join @tblClientIds ON @tblClientIds.Id = Client.ClientId
It looks OK... What error did you get?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply