VarChar Parameter used for "IN" Clause 
Author Message
 VarChar Parameter used for "IN" Clause
I want to write a stored procedure where I pass a paramter that will be used for an IN clause like this:



The column 'Dept_No' is an Integer datatype.

How should I do this?

Thanks.

George Padvorac



Tue, 23 Apr 2002 03:00:00 GMT
 VarChar Parameter used for "IN" Clause

Hi George,

Here's an example that will do this for you.


AS
BEGIN

    -- For simplicity use dynamic sql to copy into a normal temp table...

    CREATE TABLE #MyInternalList (
        list_item VARCHAR( 2 ) NOT NULL
    )

SET NOCOUNT ON

    INSERT #MyInternalList

SET NOCOUNT OFF

    -- It is now easier to join..
    SELECT *
    FROM sysobjects
    WHERE type IN ( SELECT list_item FROM #MyInternalList )
END
GO

-- To call..
CREATE TABLE #MyList (
    list_item VARCHAR( 2 ) NOT NULL
)
INSERT #MyList values ( 'S' )
INSERT #MyList values ( 'U' )
INSERT #MyList values ( 'P' )

EXEC mytest "#MyList"

You can find other similar examples at www.sql-server.co.uk

--
Best Regards

Trevor Dwyer - SQL Server MVP


Quote:
> I want to write a stored procedure where I pass a paramter that will be

used for an IN clause like this:
Quote:



varchar datatype)

> The column 'Dept_No' is an Integer datatype.

> How should I do this?

> Thanks.

> George Padvorac




Tue, 23 Apr 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. View Parameters/SQL "In" Clause

2. VFP: View Parameters/SQL "In" Clause

3. 4GL Dynamic "USING" format clause

4. Using "where" clause on table permits

5. Using VARRAY in "IN" clause

6. using the "for xml" clause

7. Parameter used in "For XML Explicit"

8. max of ("...","...","..")

9. What is difference between "=", "=*", "*=" in where clause

10. "ntext" to "varchar" using T-SQL

11. Using "sysdate" in "from" clause

12. No difference between "" and NULL varchar


 
Powered by phpBB® Forum Software