Dynamic Stored Procedures? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Stored Procedures?

Author

Topic

REDMAN
Starting Member

1 Post

Posted - 2004-09-10 : 13:30:32

I have a stored procedure that I need to execute a conditional 'WHERE' clause based on an input parameter. Basically, it's like this:
CREATE PROCEDURE sp_myproc @SUBFILTERKEY INT, @SUBFILTERVALS VARCHAR(1000) AS
SELECT * FROM MYTABLE
--Here's where I'm trying to make it dynamic
IF @SUBFILTERKEY = '1' --Filter by company
BEGIN
WHERE TableFieldCompany in @Subfiltervals
END
IF @SUBFILTERKEY = '2'
BEGIN
WHERE TableFieldSALESREP in @Subfiltervals
END
ORDER BY TableFieldPrimaryKey
GO

Of course I'm getting a syntax error because apparently you can't do this.

Any help would be appreciated...

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-10 : 13:34:54

You may be able to perform this task without using dynamic sql.


CREATE PROCEDURE sp_myproc @SUBFILTERKEY INT, @SUBFILTERVALS VARCHAR(1000) AS
SELECT * FROM MYTABLE 
WHERE (@SUBFILTERKEY = '1' AND TableFieldCompany in @Subfiltervals)
   OR (@SUBFILTERKEY = '2' AND TableFieldSALESREP in @Subfiltervals)
ORDER BY TableFieldPrimaryKey
GO

However, your going to have the problem with the IN predicate. You will need to wrap @Subfiltervals in a function that converts a CSV string to a table. You can find the function somewhere on this site.

Dustin Michaels

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-10 : 13:37:00

Here is the link http://www.sqlteam.com/item.asp?ItemID=11499

And here is the updated query.


CREATE PROCEDURE sp_myproc @SUBFILTERKEY INT, @SUBFILTERVALS VARCHAR(1000) AS
SELECT * FROM MYTABLE
WHERE (@SUBFILTERKEY = '1' AND TableFieldCompany in dbo.CsvToInt(@Subfiltervals))
   OR (@SUBFILTERKEY = '2' AND TableFieldSALESREP in dbo.CsvToInt(@Subfiltervals))
ORDER BY TableFieldPrimaryKey
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources