Simple Stored Procedure Help 1

[sonya9879]

Hi,

I have a query that I would like to move into a stored procedure but I can't find the right syntax.

Query:

SELECT ID, DATANAME, DV
FROM table_name
WHERE (ID IN (39, 40, 69)) AND (DV= 1)

The value inside in varies, sometime it comes just one value, for example 39, and sometimes it may come several different numbers, it could reach more than 300 different ID's

This is the stored procedure I originally created but doesn't work:

CREATE PROCEDURE testingprocedure
(
@ID int, (int will not work for more than one ID)
@DV int
)
AS
SELECT
ID,
DATANAME,
DV
FROM
table_name
WHERE
(ID IN (@ID))
AND
DV = @DV

Any ideas will be eternally appreciated, thanks

 

[gmmastros]

First, create this User Defined Function.

Alter Function dbo.Split
    (
    @CommaDelimitedFieldNames Varchar(8000), 
    @Character VarChar(20)
    ) 
Returns @Tbl_FieldNames 
Table     (
        Id Integer Identity(1,1),
        FieldName VarChar(100)
        ) 
As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @Character 

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@Character,@CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, your query becomes...

CREATE PROCEDURE testingprocedure
(
    @IDs VarChar(8000),
    @DV int
)
AS
Declare @IdNumbers Table(Id Integer)

Insert
Into   @IdNumbers(Id)
Select dbo.Split(@Ids, ',')

SELECT       
    ID, 
    DATANAME, 
    DV 
FROM         
    table_name
    Inner Join @IdNumbers IdNum on Table_Name.Id = IdNum.Id
WHERE
    DV = @DV

You would use it like this...

testingprocedure '1,2,3,4,5,6,7', 1


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sonya9879]

thanks so much george for your help, it perfectly works

 

[gmmastros]

Well I knew it would. I have at least a dozen stored procedures that use this function. I should warn you about this method. You notice that I used VarChar(8000) as input parameters. If the data that your parameter is holding approaches 8000 characters, then you better start looking for alternative solutions.

For example, if you are passing names, and there could be thousands of names in the list, then you will 'overflow' the 8000 character string. However, if you are passing id's from a relatively small list, the method will work fine.

I'm glad I was able to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sonya9879]

actually I will be passing thousands of ID's so that could become a problem then. George what do you recommend in that case?