String concatenation in Stored Procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Hello to All

I have a store procedure like

SQL

declare @CName as varchar(50)='SalInvNo';
declare @qry as nvarchar(500)
declare @count as int
set @qry='select @count = count(*) from table;
set @qry+=' where '+@CName+' <> 0';
exec sp_executesql @qry, N'@count int OUTPUT', @count OUTPUT

but it is giving error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SalINV1' to data type int.

[edit]Subject, tidied code blocks - OriginalGriff[/edit]

Posted 18-Mar-11 22:17pm

Sharma Richa

Updated 18-Mar-11 22:23pm

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sander Rossel · Accepted Answer · 2011-03-19T00:55:00

Solution 2

Why would you build and execute a string for this?
Executing strings is usually not best practice and there are some dangers to it as well.
http://www.sommarskog.se/dynamic_sql.html[^]
In your case I would get rid of the entire @qry stuff and simply 'hardcode' the column name into the query.

Posted 19-Mar-11 0:55am

Sander Rossel

Comments

Dalek Dave 19-Mar-11 7:17am

Good Call.

Justin Marshall Canada 23-May-17 17:22pm

hardcoding a parameter into DSQL is not safe you should use quotename built-in function to wrap your column name for example an unwanted guest could use a column name like

1=1; drop table x; select 1 where 1

this would result in the following query being run:
select @count = count(*) from table where 1=1; drop table x; select 1 where 1 <> 0

as you can see this would count every record in the db, drop table x and then return null for the 3rd part.

Also you have an extra ; after the first set @qry

Sander Rossel 23-May-17 17:25pm

Hi, not only are you replying to the wrong message, you're replying to a message from 2011 (6 years old!) that has long been resolved... Appreciate the help, but please look more closely what you're replying to :-)

Rohit from Delhi · Accepted Answer · 2011-03-18T22:30:00

Solution 1

try this

set @qry+=' where '+@CName+' <> '''+@Temp+'''';

Posted 18-Mar-11 22:30pm

Rohit from Delhi

String concatenation in Stored Procedure

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

String concatenation in Stored Procedure

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us