Procedure
-
Hi,
My proc is ,
Create Procedure GetData ( @vDate DateTime, @vStatus VARCHAR(10)
AS BEGIN
SELECT * FROM TABLE WHERE STATUS IN (@vStatus)
END
GO
EXEC getdata '2005-07-02',('I','B','C')
how i can pass my variable in sproc, i wannted use this string in sql with In CLAUSE.how to pass this kind variable to proce to run.
thanks
-
Hi,
What I usually do is create a user defined function that will convert my comma separated list into a table and then join on this table in my SQL statement.
Like this:
CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
(
@CommaSeparatedList VARCHAR(8000)
)
RETURNS @ParsedTableValue TABLE (TableValue VARCHAR(1000))
AS
BEGIN
DECLARE @TableValue VARCHAR(1000)
DECLARE @Pos INT
SET @CommaSeparatedList = LTRIM(RTRIM(@CommaSeparatedList))+ ','
SET @Pos = CHARINDEX(',', @CommaSeparatedList, 1)
IF REPLACE(@CommaSeparatedList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @TableValue = LTRIM(RTRIM(LEFT(@CommaSeparatedList, @Pos - 1)))
IF @TableValue <> ''
BEGIN
INSERT INTO @ParsedTableValue (TableValue)
VALUES (RTRIM(@TableValue))
END
SET @CommaSeparatedList = RIGHT(@CommaSeparatedList, LEN(@CommaSeparatedList) - @Pos)
SET @Pos = CHARINDEX(',', @CommaSeparatedList, 1)
END
END
RETURN
END
Then
Create Procedure GetData ( @vDate DateTime, @vStatus VARCHAR(10)
AS BEGIN
SELECT * FROM TABLE
JOIN dbo.UDF_CharCommaSeparatedListToTable(@vStatus) TABLE2 on T2.TableValue = T1.STATUS
END
GO
EXEC getdata '2005-07-02', 'I,B,C'
HTH,
Eric
-
Aiwa's way will work, and possibly is the best way to do it.
But there is another way as well. Use dynamic SQL. dynamic sql takes everything as text, so you can easily concatenate your list.
-- build your query as a text field
declare @sqlstring nvarchar(500)
set @sql = 'SELECT * FROM TABLE WHERE STATUS IN ' + (@vStatus)
execute sp_executesql @sqlstring
Let me know if this helps.
Sara
-
Hi sara,
I just try to stay away from dynamic SQL
Eric
-
Hi Eric,
I am not a fan of dynamic sql either, but sometimes it is the best solution.
Have a great weekend!
-
Thanks a lot
-
A few steps better, split - set based static sql :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply