SQL Server 2005 Stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

--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 16-Jun-12 8:58am

Faiz_Khan

Updated 16-Jun-12 20:05pm

Mohamed Mitwalli

v2

Add a Solution

2 solutions

Solution 1

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.

Posted 16-Jun-12 9:13am

OriginalGriff

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:

Faiz_Khan 17-Jun-12 7:25am

:)right

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

db7uk · Accepted Answer · 2012-06-16T09:19:00

Solution 2

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)'

Posted 16-Jun-12 9:19am

db7uk

Updated 16-Jun-12 9:22am

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?

Faiz_Khan 18-Jun-12 2:54am

@PM1 is nvarchar(50)
the @Value1 is a string (not a float, neither a datetime)
in database table it is

id nvarchar(50)
name nvarchar(100)

the procedure is for deleting record

i want to delete the row where id = 'BO-0001'
but i have a lot of table so i used a universal procedure to delete a row of a given table(testTable) and field(id)

db7uk 18-Jun-12 4:07am

Ok thanks. What is the final print output of @DynamicSQL?

Faiz_Khan 18-Jun-12 5:20am

SET @DynamicSQL ='DELETE FROM '+ @TableName +' WHERE ' + QUOTENAME(@PM1) +'=''' + @Value1 + ''''
Exec (@DynamicSQL)

as i already said
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

db7uk 18-Jun-12 5:24am

I know what you already said. But what I am asking is this.

comment out your exec(@DynamicSQL) statement.
Add Print @DynamicSQL
Tell me what the output message is.

Faiz_Khan 18-Jun-12 5:58am

exec USP_Delete_Single_PM testTable,id,BO-0001
with hyphen it prints

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

exec USP_Delete_Single_PM testTable,id,BO0001
without hyphen the print is

DELETE FROM testTable WHERE [id]='BO0001'

db7uk 18-Jun-12 6:23am

mmmm I have just re-created your sp and using the code I suggested. What happens if you run in a new query window?:

DECLARE @TableName nvarchar(50),
@PM1 nvarchar(50),
@Value1 nvarchar(50),
@DynamicSQL nvarchar(4000)

SELECT @TableName = 'testTable',
@PM1 = 'id',
@Value1 = 'BO-0001'

SET @DynamicSQL ='DELETE FROM '+ @TableName +' WHERE ' + QUOTENAME(@PM1) +'=''' + @Value1 + ''''
PRINT @DynamicSQL
Exec (@DynamicSQL)

Faiz_Khan 18-Jun-12 6:27am

its working fine there

db7uk 18-Jun-12 6:39am

Ok, Looking at how you call the SP you need to call the sp like:
exec usp_test N'test', N'id', N'BO-0001'

Faiz_Khan 18-Jun-12 6:43am

k, ..hope it works

Faiz_Khan 18-Jun-12 6:51am

yes its working
thanks a lot dear

db7uk 18-Jun-12 6:57am

my pleasure. Sorry for the time it took but got there anyway. Happy coding!

Faiz_Khan 18-Jun-12 7:04am

I'll pray for ur aircraft pilot’s license, :)

db7uk 18-Jun-12 6:26am

Ok, Looking at how you call the SP you need to call the sp like:
exec usp_test N'test', N'id', N'BO-0001'