Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

pass parameters sp_executesql

Posted on 2016-08-22
13
Medium Priority
?
76 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

876 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