Stored Procedures - Is this possible?

[zakman]

I have a VB6 program that builds SQL statements on the fly. I was hoping to put the 'core' of my SQL statement in a stored procedure and simply pass the VB-built WHERE statement to the procedure to execute. However, SQL Server doesn't like the syntax of the following:

CREATE PROCEDURE sp_test @wherestmt varchar(64)
AS
SELECT * FROM tbltest @wherestmt
GO

For example, the VB program might pass " WHERE (x = 4)" in hopes of creating "SELECT * FROM tbltest WHERE (x = 4)"

Is this possible?

Right now I simply concatenate my WHERE statement to the end of my 'core' (SELECT * FROM tbltest) and pass the entire statement to SQL Server... just trying to make use of a stored procedure in a strange sort of way...

Comments, suggestions?

 

[tlbroadbent]

You must create and execute a dynamic SQL statement if you are passing the entire WHERE clause. This might not be needed if you passed only the criteria.

Example of dynamic SQL.

CREATE PROCEDURE sp_test @wherestmt nvarchar(64)
AS
DECLARE @sql nvarchar(200)
SET @sql ='SELECT * FROM tbltest ' + @wherestmt
Execute(@sql)
GO Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[zakman]

Thank you! That worked like a charm!