[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Dynamics SQL Script with alphanumeric variables

Posted on 2007-12-03
1
Medium Priority
?
227 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

590 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