Solved

Dynamics SQL Script with alphanumeric variables

Posted on 2007-12-03
1
215 Views
Last Modified: 2008-05-28
I have this stored procedure. As long as the variable sent down is a number it works. However, if I send it an alphumeric value(i.e. C99 as opposed to 99) it errors out with the message "Invalid Column Name". How do I correct my stored procedure?


CREATE PROCEDURE [dbo].[rbsUpdateSTAXNOTE] 

@Old_CustomerID varchar(15),

@New_CustomerID varchar(15),

@Return_code int output

 

AS 

 

declare @sql nvarchar(4000)

SET @Return_Code = 1

/* If old customer ID is found, update it to the new customer ID */

IF EXISTS (SELECT * FROM STAXNOTE WHERE rtrim(CUSTOMER_NUMBER_STXN)=rtrim(@Old_CustomerID))

BEGIN

    

    SET @sql = ' update STAXNOTE ' +

               ' set CUSTOMER_NUMBER_STXN = ' + @New_CustomerID + 

               ' where CUSTOMER_NUMBER_STXN= @Old_CustomerID'

    exec sp_executesql @sql, N'@Old_CustomerID varchar(15)',@Old_CustomerID

  

END

ELSE

BEGIN

    SET @Return_Code=0

END

 

SELECT @Return_Code

 

RETURN 

 

GO

Open in new window

0
Comment
Question by:rwheeler23
1 Comment
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 20397613
rwheeler23 said:
>>               ' set CUSTOMER_NUMBER_STXN = ' + @New_CustomerID +

Change to:

               ' set CUSTOMER_NUMBER_STXN = ''' + @New_CustomerID + '''' +

Note that those are all single-quotes...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now