Solved

pass parameters sp_executesql

Posted on 2016-08-22
13
54 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
How to Install SSIS without the SQL Server CD 3 44
Merge Statement 3 39
SQL server is using more virtual memory. 5 67
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

17 Experts available now in Live!

Get 1:1 Help Now