Dynamic SQL Command: statment

i am using Dynamic SQL .
print @.sql <- It will show the statment if there is an error , Now, How can
i show the statment even there is an error
EXEC (@.sql)
Thanks a lotDont understand exactly your question, but you can print it before
executing the @.Sql, so you get the information in the messages pane (QA).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23pMdy9LSFHA.1176@.TK2MSFTNGP12.phx.gbl...
>i am using Dynamic SQL .
> print @.sql <- It will show the statment if there is an error , Now, How
> can i show the statment even there is an error
> EXEC (@.sql)
> Thanks a lot
>|||Thanks Jens,
How can I print it before excuting the @.sql ?
Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:e$pfxEMSF
HA.3444@.tk2msftngp13.phx.gbl...
> Dont understand exactly your question, but you can print it before
> executing the @.Sql, so you get the information in the messages pane (QA).
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:%23pMdy9LSFHA.1176@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
Print @.sql is good enough. Could you please post your entire part of dynamic
SQL. The error might be because of some other issues in declaration or
asssignment.
Thanks
Hari
SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23$b8JcMSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Thanks Jens,
> How can I print it before excuting the @.sql ?
> Thanks
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:e$pfxEMSFHA.3444@.tk2msftngp13.phx.gbl...
>|||Print @.sql --> Messages Pane
or Select @.sql --> Result Pane
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23$b8JcMSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Thanks Jens,
> How can I print it before excuting the @.sql ?
> Thanks
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:e$pfxEMSFHA.3444@.tk2msftngp13.phx.gbl...
>

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