CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2380844
user2380844

Reputation: 307

sql stored procedure paramater single quotes

below is the stored procedure

create procedure testpro
@screen varchar(10)
as
begin
declare @query varchar(max)
set @query = 'SELECT * FROM
--
--{code}
--

WHERE SCREEN_NAME = ' + @SCREENNAME + ' AND  Ord=3 ORDER BY date DESC'

here SCREEN_NAME = 'Normal' should be the input but in the above my procedure the query is giving input as SCREEN_NAME = Normal so its showing error since screen name is varchar.. how to give the parameter in the above code with single parameter

Upvotes: 0

Views: 114

Answers (2)

bgs
bgs

Reputation: 3223

Try this:

Just remove single quote '' from your query..

set @query = 'SELECT * FROM' 
{ Code Here }
@query = @query +' WHERE SCREEN_NAME = ''' + @SCREENNAME + ''' And Ord=3  Order By date Desc'

Upvotes: 1

Satpal
Satpal

Reputation: 133453

You don't need to wrap parameter in quotes ''. So simply remove quotes and use

WHERE SCREEN_NAME = @SCREENNAME  ORDER BY date DESC

EDIT

You need to escape ', I have not tested it

Use

 WHERE SCREEN_NAME = ''' + @SCREENNAME + ''' AND  Ord=3 ORDER BY date DESC'

Upvotes: 3

Related Questions