[RESOLVED] Dynamic SQL Stored Proc issue

Hey all,

I have a stored procedure where I build an SQL string in SQL Server. I have it parameterized on the asp.net side of things but I am getting an error when I send anything with a bad character to the parameter. This is the line I am having trouble with:

SET @sSQL = @sSQL + ' AND LastName LIKE ''' + @LastName + ''''

I passed O'Sullivan and I get a syntax error because of the apostraphe. How would I fix this?

Thanks

Anytime you create dynamic sql strings you need to account for these types of problems. In this case use the Replace function on @Lastname and convert the apostrophe to two apostrophes.

Another option is to use sp_ExecuteSQL, which allows you to create parameterized queries with dynamic sql statements.

Code:

---

Declare @LastName nvarchar(100)
Declare @SQL nvarchar(1000)

Set @SQL = 'Select * From Customers Where CompanyName like @Parm1'
exec sp_ExecuteSQL @SQL, N'@Parm1 varchar(100)', @Parm1 = @Lastname

---

I think your stuck with using a replace to change the single qoute (apostraphe) to two single qoutes.

Thanks for clearing that up guys...

I did go with the replace function.

Depending on what is in your SP, you may not need dynamic SQL at all... if your reason for using it is so that you can have things like this:

Code:

---

IF @LastName Is Not Null
BEGIN
  SET @sSQL = @sSQL + ' AND LastName LIKE ''' + @LastName + ''''
END

---

..you can avoid using slow/unsafe dynamic SQL by changing it to the SQL equivalent:

Code:

---

AND  (@LastName Is Null OR LastName LIKE @LastName)

---

If the parameter is Null the Like part will not be checked (as the OR means the entire line is True), and if the parameter is not Null you will be doing the same check you were before.

Yes that is exactly why it is a dynamic string. It is legacy code that I'd rather not rewrite at the moment but I do appreciate the tip.

Thanks!

Thank you guys! I just posted this exact question about an hour ago and your solution helped me too! Sorry I didn't search first...