Click here to Skip to main content
15,881,424 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
--here is my Stored Procedure (SQL2005)
SQL
ALTER PROCEDURE [dbo].[USP_Delete_Single_PM] 

@TableName nvarchar(50),	
@PM1 nvarchar(50),
@Value1 nvarchar(50)
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @DynamicSQL nvarchar(4000)
   SET @DynamicSQL =	'DELETE FROM '+ @TableName +' WHERE ' + @PM1 +'=' + @Value1 
   Exec (@DynamicSQL)
END


--when i execute with
--USP_Delete_Single_PM testTable,id,BO-0001
--it gives error as
--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near '-'.
--Its working fine if there is no hyphen in the value

--I cant change the id BO-0001 to BO0001 as it is used every where.
--Any solution, Please dont say to remove '-' from the id(BO-0001).

thanks in anticipation
Faiz
Posted
Updated 16-Jun-12 20:05pm
v2

You are not stating what the @Value1 is. Is it int, varchar, numeric etc.

Basically a starting point would be to have single quotes around the value.

SQL
'DELETE FROM '+ @TableName +' WHERE ' + QUOTENAME(@PM1) + ' = ''' + @Value1 + ''''


Also note QUOTENAME. This should be used to specify columns in SQL dynamic queries.

Your output would then be:

DELETE FROM testTable WHERE [Id] = 'B0-23232'

But going back to my original point. Dynamic sql will have problems if say your column is a datetime and you pass a string value that can not be converted to a datetime. You should consider casting or converting specific values to their value types like:

convert(datetime, '23/12/2011', 103) would be in your where clause:

SQL
'DELETE FROM '+ @TableName +' WHERE [UpdatedDate] = convert(datetime, ''' + @Value1 + ''', 103)'
 
Share this answer
 
v2
Comments
Faiz_Khan 17-Jun-12 5:05am    
@Value1 = BO-0001
as i have already said im executing it with
USP_Delete_Single_PM testTable,id,BO-0001

thnx for the answer
db7uk 17-Jun-12 5:14am    
yes but you are missing the quotes ''' around the value.
Faiz_Khan 17-Jun-12 5:20am    
yes, thnx, and pls go through my next question i asked above from OriginalGriff
Faiz_Khan 17-Jun-12 14:18pm    
not working even after quotes
db7uk 17-Jun-12 18:02pm    
If you do a Print @DynamicSQL what does the message tell you? Could you also state the correct datatype for @Value1 (I take BO-0001 is varchar) and specifically @PM1?
Quote it:
SQL
SET @DynamicSQL = 'DELETE FROM '+ @TableName +' WHERE ' + @PM1 +'=' + @Value1
becomes:
SQL
SET @DynamicSQL = 'DELETE FROM '+ @TableName +' WHERE ' + @PM1 +'=''' + @Value1 + ''''

This surounds your @Value1 value with single quotes, so the hyphen is no longer recognised.
 
Share this answer
 
Comments
Faiz_Khan 17-Jun-12 5:04am    
thnx
OriginalGriff 17-Jun-12 5:07am    
You're welcome!
Faiz_Khan 17-Jun-12 14:17pm    
its not working, pls check urself
Faiz_Khan 17-Jun-12 5:14am    
I have another Question Related to Asp.net 3.5 using C#

I have a textbox at the top of a gridview, when i press a key, the gridview filled with the data related to the text in the textbox.
now i have to click the gridview with mouse to go up and down with arrow keys
I want that when i press down arrow key in the textbox the first row of the gridview is automatically get clicked and the focus goes to the first row(if exist)
OriginalGriff 17-Jun-12 5:58am    
Open a new question instead of asking a different one as a comment - since they are not related, it makes it harder for others with the same problem as you to find the solution if you don't! :laugh:

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900