enrique_aeo
asked on
pass parameters sp_executesql
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
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
ASKER
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
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
ASKER
Please, your support, i attached data
create_table_data---copia.zip
create_table_data---copia.zip
Can you explain the new problems? This seems to work perfectly for me locally on my system.
ASKER
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.
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.
I copied and pasted all of that to my local SSMS and it works, but there is no definition for:
sp_executesql
In your example that you posted. Do you have a more fully detailed demonstration of the problem?
ASKER
i attached the image
ssms.png
ssms.png
ASKER
Dear
Daniel Van Der Werken
you say it works OK, I could send the code to run.
Daniel Van Der Werken
you say it works OK, I could send the code to run.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the collation is: Latin1_General_CS_AI
i change @Lname to @LName
it is Works.
DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformat ion] @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
i change @Lname to @LName
it is Works.
DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformat
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
ASKER
thanks
https://msdn.microsoft.com/en-us/library/ms188001.aspx
You'd want to create the declaration for your parameter definition:
Open in new window
then,
Open in new window
and then create a variable for each of your input variables,
Open in new window
and so forth,Call it:
Open in new window