Solved

Dynamics SQL Script with alphanumeric variables

Posted on 2007-12-03
1
216 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

943 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

7 Experts available now in Live!

Get 1:1 Help Now