Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pass parameters sp_executesql

Posted on 2016-08-22
13
Medium Priority
?
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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