Using a table variable as input in a stored procedure

6,360 pts.
Tags:
SQL Server
Stored Procedure
Stored Procedure variables
VB .NET
Have just run into a problem. Currently our web service has been adding data to our SQL server 2008 tables by building a dynamic SQL INSERT command. It has started failing because of the data coming in. It contains a single quote in the name data ex. O' Brien. I am looking to pass the tables from the dataset into different stored procedures (1 for each of the 4 tables) to eliminate this "glitch" but not having much luck. I'm trying to insert into the same table that we were using the dynamic SQL process. Any pointers would be appreciated as it's my first time trying to use a table variable as an input parm. Here is my stored procedure code.
USE [MyDatabase]
GO

/****** Object: StoredProcedure [dbo].[NewOrderHeaderData] Script Date: 09/18/2013 10:04:33 ******/ 

SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

CREATE TYPE NewOrderHeaderData AS TABLE 
(
OrderID INT NOT NULL, 
UserID INT NULL, 
SoldToCompany nvarchar(128) NULL, 
SoldToAddress nvarchar(128) NULL, 
SoldToCity nvarchar(128) NULL, 
SoldToState nvarchar(128) NULL, 
SoldToZipCode nvarchar(128) NULL, 
SoldToCountry nvarchar(128) NULL, 
ShipToCompany nvarchar(128) NULL, 
ShipToAddress1 nvarchar(128) NULL, 
ShipToAddress2 nvarchar(128) NULL, 
ShipToAddress3 nvarchar(128) NULL, 
ShipToCity nvarchar(128) NULL, 
ShipToState nvarchar(128) NULL, 
ShipToZipCode nvarchar(128) NULL, 
ShipToCountry nvarchar(128) NULL, 
RepNumber nvarchar(128) NOT NULL, 
CustomerNumber nvarchar(128) NOT NULL, 
CustomerPONumber nvarchar(128) NOT NULL, 
MarkTag nvarchar(128) NULL, 
RepPONumber nvarchar(128) NULL, 
Notes nvarchar(128) NULL, 
WebTicket nvarchar(128) NULL, 
OrderNumber nvarchar(128) NULL, 
DateCreated datetime NULL, 
Company nvarchar(128) NULL, 
Division nvarchar(128) NULL, 
Warehouse nvarchar(128) NULL, 
PriceAs nvarchar(128) NULL 
) 
GO 

-- ============================================= 
-- Author: 
-- Create date: 
-- Description: 
-- ============================================= 

CREATE PROCEDURE [dbo].[NewOrderHeaderData] 
@TableValueParms NewOrderHeaderData READONLY 

AS 

SET NOCOUNT ON 

INSERT INTO [dbo].[OrderHeader] 
(OrderID,UserID,SoldToCompany,SoldToAddress,SoldToCity,SoldToState,SoldToZipCode, 
SoldToCountry,ShipToCompany,ShipToAddress1,ShipToAddress2,ShipToAddress3,ShipToCity,ShipToState, 
ShipToZipCode,ShipToCountry,RepNumber,CustomerNumber,CustomerPONumber,MarkTag,RepPONumber,Notes, 
WebTicket,OrderNumber,DateCreated,Company,Division,Warehouse,PriceAs) 
SELECT * from @TableValueParms; 
Go 

DROP TABLE NewOrderHeaderData


Software/Hardware used:
SQL Server 2008
ASKED: September 18, 2013  7:01 PM
UPDATED: September 23, 2013  12:16 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Hi Todd.

    I'm not an SQL Server expert, so I'm not sure I will be able to help, but I have a couple of questions for you:

    How exactly are you eliminating the glitch by passing the data into stored procedures using the table variable? Is it not possible to just escape the quotes when performing the INSERT?

    Also, are you getting errors when running the stored procedure?  Can you post the error messages?
    65,110 pointsBadges:
    report
  • ToddN2000

    Building the insert dynamically, the single quote causes an invalid SQL command. Same principal as SQL injection.

    By doing this in a stored procedure, and not dynamically the single quote will be kept. This will let us keep things the right way. We can keep the company name like "Dave's Plumbing" instead or replacing the quote and having "Dave s Plumbing". I'm doing the replace now but want a permanent fix.

    6,360 pointsBadges:
    report
  • carlosdl
    But you could replace the data to escape the quotes (not to remove them).

    Something like this:

    set @sql = 'insert into YourTable values (<some values> + replace(@name,'''','''''') +<any other values>';
    exec(@sql);
    Dynamic SQL should be avoided for security reasons, but if you are currently using and it does not pose risks in your specific case, it could be an option to just make it work and continue using it.

    As for the table parameter:  I have not done that, so just looking at the code I'm not able to identify the problem.  What errors are you getting when trying that approach?
    65,110 pointsBadges:
    report
  • ToddN2000
    Thanks carlosdl, I have 2 issues. It seems it would work if the server I'm connecting to was 2008. It's SQL Server 2000. So the next thing is to convert the dynamic code to stored procedures. I made those and it works fine to a point. Now that the work tables are built, I need to extract that data and send it to an i-Series.  Now I have the same issue, the old code does it dynamically. Guess I have to convert the 4 table updates to stored procedures as well. Not going to be fun, the procedures will require 107 parms, 73, 28 and 8.. Guess it's time to get coding.!! 
    6,360 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following