Dynamic SQL Command: dynamic SQL error

Help,
I have the following procedure declared as a test for dynamic sql. The
resulting SQL statment is correct but I am getting syntax errors when the
dynamic SQL runs. taking the body of the procedure and running it from SQL
Analyzer window
creates the correct result. If I rewrite it to remove the parameters it
alos runs correctly. What am I missing to have this run corrcetly with
parameters
Declare @.Accountname varchar(50)
DECLARE @.@.OrgID int
DECLARE @.@.MonthID int
DECLARE @.@.W

int
DECLARE @.@.AccountID int
DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
Set @.@.Orgid=6
Set @.@.Monthid=1
Set @.@.W

=2
Set @.@.Accountid=3
Set @.Accountname = 'IP'
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW

], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

= 2,
@.@.AccountID= 3
End
create PROCEDURE [dbo].[test]
AS
Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW

], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, (SELECT
[DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W

))
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

= 2,
@.@.AccountID= 3
print @.SQL
End
EXEC [HYP-MOR].[dynasight].[TEST]
yeild the following result
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.@.OrgId'.
INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW

],
[DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountIDHi,
are your sql server in collation CS ? Wich I think so ?
So there is a mismatch between the declare @.@.OrgID and the use in your
stored proc : @.@.OrgId
A +
ahuntertate a écrit :
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W

int
> DECLARE @.@.AccountID int
> DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
> Set @.@.Orgid=6
> Set @.@.Monthid=1
> Set @.@.W

=2
> Set @.@.Accountid=3
> Set @.Accountname = 'IP'
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW

], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

=
2,
> @.@.AccountID= 3
> End
>
>
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW

], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, (SELECT
> [DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W

))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

=
2,
> @.@.AccountID= 3
> print @.SQL
> End
>
> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.@.OrgId'.
> INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW

],
> [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I get a different error message (stating that the table doesn't exist, which
is correct). Make sure
that you use correct casing. Perhaps you are on a case sensitive SQL Server.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ahuntertate" <ahuntertate@.discussions.microsoft.com> wrote in message
news:8B7E4A21-FA48-4BA5-8DC6-104666C3C1E5@.microsoft.com...
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W