Hi. I'm attempting to write a Stored Procedure that creates an SELECT
statement. Belwo is an example of what I am tryign except that my actually
query have many more parameters most of which can be eliminated.

@sQUERy NVarchar(2000),
@sParams NVarchar(2000)

@sQuery = 'SELECT * FROM Clients WHERE '

IF ISNULL(@sFirstName,'') <> ''  BEGIN
    @sQuery = @sQuery+' FirstName LIKE @sFirstName '
    @sParams = ' @sFirstName VARCHAR(30),'
END

IF ISNULL(@sLastName,'') <> ''  BEGIN
    IF LEN(@sParams) > 0
        @sQuery = @sQuery+' AND LastName LIKE @sLastName '
    ELSE
        @sQuery = @sQuery+' LastName LIKE @sLastName '

    @sParams = ' @sLastName VARCHAR(30)'
END

  IF LEN(@sParams) > 0
        @sQuery = @sQuery+' AND Cancelled=0'
    ELSE
        @sQuery = @sQuery+' Cancelled=0 '

    @sParams = ' @Cancelled    SMALLINT'

EXEC sp_executesql @sQuery,@sParams, <WHAT DO I PUT HERE??>

Can it even work?

Thanks for any suggestions!

[posted and mailed, please reply in news]

Lisa Forde (1fo***@caribsurf.com) writes:
You put the parameters. I have an example of a dynamic search routine
at http://www.sommarskog.se/dyn-search.html#sp_executesql which hopefully
can be of help for you.

Perfect!

Thanks.

Show quoteHide quote

Finding Correct Primary Key
Performance Problem
Inserting records and running a stored procedure at the same time?
Internal SQL Server error
COM+ Applications hanging