Solved

pass parameters sp_executesql

Posted on 2016-08-22
13
46 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 19

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
 
LVL 19

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 19

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 19

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 19

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

19 Experts available now in Live!

Get 1:1 Help Now