Dynamic SQL Statements in Stored Procedures

[lmf232s]

I would like to remove my dynamic sql statements from code and place them in a stored procedure.

I found this article which talks about it and shows a very simple example of passing in 1 parameter.
http://www.4guysfromrolla.com/webtech/102300-1.shtml

Unfortunetaly im in a possession where i will need to pass in multiple parameters where there's a chance that only 1 may have a value.

I thought about maybe just adding all the fields and doing a Like on all of them (this query in question will be a like search as im searching form First name, Last name, company name, etc) but this just does not seem like a very good structure.

So i thought i would see what you guys think.

The basic structure of the query i need to try to implement is along the lines of this and then convert this to a stored procedure. If nothing else i can just build the sql statement in the business layer but i thought i would try to put it in a SP.

sSQL = "SELECT * FROM TABLE WHERE "
If FistName <> "" Then
sSQL = sSQL & " FirstName Like '" & FirstName & "%'"
End If
ETC.........

[lmf232s]

Well this looks like it works and im going to go with this unless i hear differently from the community. From what i read its better to execute
the statement using sp_executesql insead of just doing an EXEC and then your sql statement.

Although you wanna talk about hard to read. In some of the articles i read
they talked about how dynamic sql can be hard to read. Well while this is a
pretty simple example, i can see that as this gets more complex it will be
just as hard to read. Pluss with dynamic sql in your pages you can just do a
response.write sql to see what sql statement is trying to execute. Here it
looks like it could be a pain to debug.

Let me know what you guys thinks. I dont have a problem doing it like this
but i might just create my dynamic sql in the business layer and let it be.

CREATE PROCEDURE sp_QuoteSystem
@FirstName varchar(100)

AS
DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT * ' +
' FROM CONTACT WHERE '

IF @FirstName IS NOT NULL
SELECT @sql = @sql + ' FirstName LIKE @FirstName '

EXEC sp_executesql @sql, N'@FirstName varchar(100)', @FirstName

RETURN
GO

[russell]

this looks good. often when there is just one param, i just use dynamic sql in my sp. if there is more than one, i go the route you demonstrated above. a common example of one param and dynamic sql is sorting -- for example:

Create Proc usp_myProc
@sort varchar(32) = 'id'
AS

EXEC('
SELECT id, name, dob
FROM Employees
ORDER BY ' + @sort
)
GO That said, your way is the "Right Way" and it allows output parameters too, which isn't possible with dynamic sql.

i would suggest not prefixing your SPs sp_, use something else -- i use usp_

why? if it is prefixed sp_ then mssql thinks it's a system stored procedure and looks for it in master before searching the current db for it. also makes it easier to find your SPs in query analyzer as they are sorted by name, they come after all of the system SPs.