Solved

pass parameters sp_executesql

Posted on 2016-08-22
13
58 Views
Last Modified: 2016-09-15
hi experts

how to pass parameters to this store procedure ?

http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/

i am trying, but is not working
declare @spexecutesqlStr nvarchar(1000)
 EXEC [sp_executesql] @spexecutesqlStr
 , N'@CustID bigint, @LName varchar(30), @FName varchar(30)
 , @MI char(1), @Email varchar(128), @RegionNo tinyint
 , @CustomerCode tinyint'
 , @CustID = null
 , @LName = null
 , @FName = 'Achong'
 , @MI = null
 , @Email = null
 , @RegionNo = null
 , @CustomerCode = null;

GO
0
Comment
Question by:enrique_aeo
  • 8
  • 5
13 Comments
 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 41766034
The mechanism for dynamic SQL with parameters is as follows:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

You'd want to create the declaration for your parameter definition:

DECLARE @ParmDefinition nvarchar(500);

Open in new window


then,

SET @ParamDefinition = N'@CustID bigint, @LName varchar(30), @FName varchar(30).....'

Open in new window


and then create a variable for each of your input variables,

DECLARE @CustomerID BIGITINT = 123456789
DECLARE @LastName VARCHAR(30) = 'Smith'

Open in new window

and so forth,

Call it:
EXEC sp_executesql @spexecutesqlStr , @ParamDefinition, @CustID = @CustomerID,
@Lname = @LastName, .....

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 41767121
i execute this

declare @spexecutesqlStr nvarchar(1000)

DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'

DECLARE @CustomerID BIGINT = 1
DECLARE @LastName VARCHAR(30) = 'Achong'

EXEC sp_executesql @spexecutesqlStr , @ParmDefinition,
            @CustID = @CustomerID,
            @Lname = @LastName,
            @FName = null,
            @MI = null,
            @Email =null,
            @RegionNo = null,
            @CustomerCode= null


show this
Command(s) completed successfully.
User_GetCustomerInformation.txt
0
 

Author Comment

by:enrique_aeo
ID: 41769246
Please, your support, i attached data
create_table_data---copia.zip
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 41774942
Can you explain the new problems? This seems to work perfectly for me locally on my system.
0
 

Author Comment

by:enrique_aeo
ID: 41780659
i execute this

declare @spexecutesqlStr nvarchar(1000)

 DECLARE @ParmDefinition nvarchar(500);
 SET @ParmDefinition = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'

 DECLARE @CustomerID BIGINT = 1
 DECLARE @LastName VARCHAR(30) = 'Achong'

 EXEC sp_executesql @spexecutesqlStr , @ParmDefinition,
             @CustID = @CustomerID,
             @Lname = @LastName,
             @FName = null,
             @MI = null,
             @Email =null,
             @RegionNo = null,
             @CustomerCode= null

the error is:
Msg 8178, Level 16, State 1, Line 0
The parameterized query '(@CustID bigint, @LName varchar(30), @FName varchar(30), @MI cha' expects the parameter '@LName', which was not supplied.
0
 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 41781708
I copied and pasted all of that to my local SSMS and it works, but there is no definition for:
sp_executesql 

Open in new window

In your example that you posted. Do you have a more fully detailed demonstration of the problem?
0
 

Author Comment

by:enrique_aeo
ID: 41782236
i attached the image
ssms.png
0
 

Author Comment

by:enrique_aeo
ID: 41795249
Dear
Daniel Van Der Werken

you say it works OK, I could send the code to run.
0
 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 41795901
All my local testing has worked with your examples. I can't determine what the problem is. Sorry. It works just fine in my local system. Could it be that we have different versions of SQL server that are causing the problems? I'm using 2012.
0
 

Author Comment

by:enrique_aeo
ID: 41799738
i am using:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
      Feb 20 2014 20:04:26
      Copyright (c) Microsoft Corporation
      Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Please send me T-SQL code that are running
0
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 500 total points
ID: 41799758
This works for me:

DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC dbo.GetCustomerInformation @CustomerID = @CustID, @LastName = @Lname'
DECLARE @ParmDefinition NVARCHAR (500) = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'
DECLARE @CustomerID BIGINT = 1
DECLARE @LastName VARCHAR(30) = 'Achong'

 EXEC sp_executesql @spexecutesqlStr, 
					@ParmDefinition, 
					@CustID = @CustomerID,
					@Lname = @LastName,
					@FName = null,
					@MI = null,
					@Email =null, 
					@RegionNo = null, 
					@CustomerCode= null

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 41799776
the collation is: Latin1_General_CS_AI
i change @Lname to @LName

it is Works.

DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformation] @CustomerID = @CustID, @LastName = @Lname'
DECLARE @ParmDefinition NVARCHAR (500) = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'
DECLARE @CustomerID BIGINT = 1
DECLARE @LastName VARCHAR(30) = 'Achong'

 EXEC sp_executesql @spexecutesqlStr,
                              @ParmDefinition,
                              @CustID = @CustomerID,
                              @LName = @LastName,
                              @FName = null,
                              @MI = null,
                              @Email =null,
                              @RegionNo = null,
                              @CustomerCode= null


thanks
storeDynamic.txt
0
 

Author Closing Comment

by:enrique_aeo
ID: 41799777
thanks
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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