Sql Varibales and apostrophes - 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)

Sql Varibales and apostrophes

Author

Topic

morserya
Starting Member

2 Posts

Posted - 2005-05-13 : 10:43:33

I have the following stored procedure that I'm calling from an asp page.

Here's how it works. I only have the variables being sent if they contain information. I'm handling this in the asp page. If they do have information I want to have the following text in a sql variable
text= and shape_desc like '%@shape_desc%'

Question: Can I have an SQL variable contain another SQL Variable and how do I hande the apostrophes in that variable?

My following example doesn't work because of the apostrophe's

-----------------------------
CREATE proc getdrawingdata
@shape_desc nvarchar
As
@shape_desc_sql nvarchar
@shape_desc_sql = 'and shape_desc like '%@shape_desc%''

Select * from drawing, customer where
drawing.prefix = customer.prefix and @shape_desc_sql
GO
------------------------------

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-13 : 10:49:07

your example doesn't work because
a) @shape_desc_sql isn't declared
b) you tried to put 'and' in your where clause twice in a row
c) you are trying to mix regular SQL with dynamic SQL

dynamic SQL is when you build a string and then execute that string

you don't need dynamic SQL to do this, all you need to do is:


CREATE proc getdrawingdata
@shape_desc nvarchar 
As

Select * from drawing, customer where 
drawing.prefix = customer.prefix and shape_desc like '%' + @shape_desc + '%'
GO

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

morserya
Starting Member

2 Posts

Posted - 2005-05-13 : 13:26:20

It's a little more complicated than that. I guess my last post wasn't completely descriptive.

I have people searching on 6 different fields possibly. If the field is populated I want to add the [and shape_desc = %' +@shape_desc + '%] and if it's not populated then I want it to = nothing just a blank space so I can keep appending [and something = %' + @something '%]. I just really need to know how to handle an apostrophe in a variable without it screwing up my statement.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-13 : 14:09:59

you need to replace single quotes with double quotes for exec'd sql:


declare @parm varchar(50)
	,@sqlStr varchar(200)

set @parm = 'O''hare'

select @parm

--replace single quotes with double quotes
set @parm = '''' + replace(@parm, '''', '''''') + '%'''

set @sqlStr = 'AND airport like ' + @parm

select @sqlStr

Be One with the Optimizer
TG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-13 : 17:10:12

Select * from drawing, customer
where drawing.prefix = customer.prefix
and (shape_desc like '%' + @shape_desc + '%' or @shape_desc is null)
...etc

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources