Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing Dynamic SQL with out Select Permission

I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_executesq
l
@.sql I am executing the dynamic sql, few of my procedures are getting input
parameter for table name and/or column names also. Now the database user is
modified with privileges, he has assigned only execute Permission. How to
solve this problem.You can't. If you use dynamic SQL you need permissions on the underlying
tables. Passing in table names and column names as parameters to a stored
procedure is not a good idea anyway, and the problem you have run into is
only one of the issues (see http://www.sommarskog.se/dynamic_sql.html). If
you can explain what you are actually trying to do, someone here can come up
with a better solution.
Jacco Schalkwijk
SQL Server MVP
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
>I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
>sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
> input
> parameter for table name and/or column names also. Now the database user
> is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||Unfortunatly you can't, with dynamic SQL you must have Select Permission on
the table.
If you tell us what you are tryng to do we could possible sugest an
alternative.
Peter
Do not arouse the sleeping dragon, for you are crunchy and taste good with
ketchup.
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also.
Care to explain just WHY you are doing that? The usual reasons are poor
database design and/or poor coding practices. The solution is almost always
not to do it. Dynamic SQL comes with a lot of incovenient baggage: security
vulnerabilities; performance implications; maintenance and reliability
issues; cost to develop and support.
David Portas
SQL Server MVP
--
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||I don't know if this applies in your case, but it helped avoid dynamic SQL
on a project of mine. If you need to query across multiple partitioned
tables (ex: SALES_2004, SALES_2003, etc), then consider using a partitioned
view (basically a view of unionized tables). When a new table is added, then
you can re-create the view that includes the new table reference.
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
input
> parameter for table name and/or column names also. Now the database user
is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.

Executing Dynamic SQL larger than 8000 characters

Can anyone tell me if there is a way to get around the 8000 character limit for executing dynamic SQL statements? I have tried everything I can think of to get around this limitation but I can not figure out a way around this.

Here are a few of the things that I have tried that have not worked

Using VARCHAR(MAX) instead on VARCHAR(8000)

Using NVARCHAR(MAX) instead of NVARCHAR(4000)

Using nTEXT (BLOBs are not support for variables)

Executing the statement via .NET using the SqlCommand.CommandText (it accepts a data type of String which is limited to 8000 characters)

I can't believe this is sooo hard to figure out. I know somebody has run into this before. All help would be greatly appreciated.

Create multiple 8000 char strings, break your string into 8000 char blocks and run "EXEC (@.sql1+@.sql2+@.sql3+.......)"|||

Tom,

Thanks for the help! However, that did not work either. My query is 8621 chars long I broke the query into two VARCHAR(8000) variables, one was 7900 and the other was 721. Here is the error:

The character string that starts with 'SELECT ...' is too long. Maximum length is 8000.

Not sure why it is not working for me if it works for you... what is the data type fo the variables that you are using?

|||I haven't seen that error before. However, I am usually executing multiple "commands", not 1 single command greater than 8000 chars. That might be a limitation of SQL, the command buffer might only be 8000 chars.

Maybe someone from MS can answer if that is a "command buffer limit"?

You might have to break it further into multiple select statements.|||

Can you post the code. There shouldn't be a problem executing sql statement larger than 8000 via exec().

e.g.

declare @.a varchar(8000),@.b varchar(8000),@.c varchar(8000)
select @.a='select top 1 name,''',@.b=replicate('a',8000),@.c=''' from sysobjects'
exec(@.a+@.b+@.c)

|||

varchar(max) also should work just fine - could you please try something like the following?

declare @.cmd varchar(max)
set @.cmd = 'print /*' + replicate ('-', 7990);
set @.cmd = @.cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@.cmd)
print datalength (@.cmd)

Feb 2 2007 2:23PM
16000

|||you have to use the new sys.sp_sqlexec stored proc that accepts a parameter of type text. have used this on a numberof occassions with sql strings in excess of 8k limit.|||Thanks for all the help. Looks like I have several options here.

EXECUTING A STRING (URGENT HELP PLEASE)

Dear friends,
i have a problem here that as much as i go through it looks worth.
I have build a dynamic query with a string like below:
[blue]
CREATE PROCEDURE PROC1
AS
DECLARE @.TempQString nvarchar(1000)
SET @.TempQString = 'DECLARE @.resultvalue int'
SET @.TempQString =@.TempQString + 'EXEC @.resultvalue=[MyStoredProcedure]'
EXEC sp_executesql @.TempQString
GO
[/blue]
now in the body of my main stored procedure (PROC1) i want to get the
return value if the [MyStoredProcedure] that was executed through a string!!!!!!!!!!
i've tried to insert the return value in a temp table (#table)
but this also didnt work, cuz out of the string execution my temp table was dropped!! also i cant use a global temp table (##table)
because many users may execute the PROC1 at the same time!!
[red]PLEASE HELP THIS IS VERY IMPORTANT FOR ME[/red]

Firstly I have to question why you are execution your stored procedures in this way as it doesn't seem to be particularly efficient. Is there another way you can perform the task?

If you still need to use this method then try the example below.

Chris

Code Snippet

CREATE PROCEDURE PROC1

AS

DECLARE @.TempQString nvarchar(1000)

DECLARE @.resultvalue INT

SET @.TempQString = 'EXEC @.resultvalue=[MyStoredProcedure]'

EXEC sp_executesql @.TempQString, @.Parameters = N'@.resultvalue int output', @.resultvalue = @.resultvalue OUTPUT

RETURN ISNULL(@.resultvalue, -1)

GO

sql

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?
Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

execute stored procedure durning merge replication

Hello
I have merge replication with dynamic filters and I have to create
dynamically table just before merge replication starts. Is there any
possibility of exec stored procedure durnig merge replication or just
before it?
Thanks in advance,
marcin
Hi
My problem is more complicated.
I create dynamicly subscription from Pocket PC where I set Host_Name for
dynamic filters.
How can I add merge agent job from source code ?
Any ideas ?
Thanks,
Marcin
Paul Ibison wrote:

> Sure - just add another step to the merge agent's job and
> change the workflow accordingly. I have had issues with
> using sp_start_job in job steps - there is no guarantee
> that a step will complete fully before the next one
> starts, but in the case of a script call this should be
> ok.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

execute ssis

Hi,

In the SSIS package, I have made the filename dynamic i.e. set as a variable.

How is it possible to do the same thing for the database name in the oledb connection?

I looked at the connectionString property for the OLEDB connection. ConnectionString looks long and the databasename is in this text.

Not sure how to make this databasename inside the connectionstring dynamic?

Thanks

hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +
' \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'
print @.cmd

error is:
Option "\Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1\databasename" is not valid.

please note I just retyped the data source name here.

|||

Get the raw command line working first then port it to SQL version. You are setting two properties, so try giving each it's own /SET. Also try quoting the property ID string bit.

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

I just mocked that up using DTEXecUI, as it can almost act like a command line builder tool for you.

|||

Hi,

It works if I only pass the filename variable but not with both parameters.

As you see I am using the /set option.

Can you see what is wrong with what I sent initially please?

Thanks

|||

You can just replace the entire connection string. Perhaps save the package with a default connection in a variable and then use an expression and REPLACE to change the place holder database for the real one, or just save a connnection string wout the database and add in via and expression. So I guess the trick is to manage the connection string differently, perhaps as sections, such that you can construct what you need with expressions latter. Not ideal, but no doubt driven by the nature of connection types, and connection string properties rather than the known common properties.

If using configurations you can change the InitialCatalog property, but unfortunatey this is not available as the target for a property expression.

|||

I can see you used one set option, but passed two sets against it.

Look at my example again and you will see that I have two /SET options in there, one for each property to be set.

Error -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" "\Package.Variables [User::ConnectionPath].Properties[Value]";"XX XX"

Option "\Package.Variables[User::ConnectionPath].Properties[Value];XX XX" is not valid.

OK -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

|||

I am getting close to what I was after.

Basically I am passing a variable but not sure how to pass two variables. See below. Do you see what is wrong with the below query please?

It works if filename variable is used but not if the second variable is included.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

' /set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

|||

Get the same error.

This is what I have:

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

'/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

|||Can you show us the result of "print @.cmd"?|||

dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\TradeCreds.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\CollateralEx.csv"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=server1\instance1, 2025;Initial Catalog=database1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||What is this?

Data Source=server1\instance1, 2025;|||

I replaced the actual servername\instance

|||

arkiboys wrote:

I replaced the actual servername\instance

What is ", 2025"|||

port no.
This is how I connect to the database.

|||

I think I worked it out:

set @.cmd = 'dtexec /f ' + @.FullPackagePath +

' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Execute Big Dynamic SQL in Stored Procedure to Create View

I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure creates
a string of custom SQL statement and returns this string back to the
main stored procedure.

This SQL statements work fine on there own. The SQL returned from the
sub stored procedure are returned fine. The datatype of the variable
that this sql is stored in Varchar(I have tried using nvarchar also
same problem).

If I have more that 6 SQL statements concated then the main SQL gets
cut off. It doesnt matter in what sequence I create the main SQL.

Here is the Stored procedure

/**********************************************/
/*Main Stored Procedure */

/**********************************************/
CREATE PROC sp_generate_invoice1 @.prev_date NVarchar(1000) ,
@.prev_month NVarchar(32)
AS

DECLARE invoice_driver_cur CURSOR FOR
Select driversid From Invoice_drivers

Open invoice_driver_cur

Declare
@.C VARCHAR(8000),
@.L_args Varchar(8000),
@.@.sqlstmt Varchar(8000),
@.L_driverid Int,
@.L_rowcount Int

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 32768

Set @.L_rowcount = 0

-- Drop the previous View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'custom_invoice')
DROP VIEW custom_invoice

Fetch Next From invoice_driver_cur
Into @.L_driverid

-- Create the new View
Set @.L_args = N'Create View custom_invoice As'

--Select @.L_driverid

WHILE( @.@.FETCH_STATUS = 0)
BEGIN
Set @.L_rowcount = @.L_rowcount + 1
select @.L_driverid
If @.L_driverid = 2
Begin
Exec sp_invoice_driver2 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.L_args + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 3
Begin
Exec sp_invoice_driver3 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 4
Begin
Exec sp_invoice_driver4 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 5
Begin
Exec sp_invoice_driver5 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 6
Begin
Exec sp_invoice_driver6 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 7
Begin
Exec sp_invoice_driver7 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 8
Begin
Exec sp_invoice_driver8 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 10
Begin
Exec sp_invoice_driver_niku @.prev_date, @.prev_month, @.L_driverid,
@.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.C + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End
Print @.C
Fetch Next From invoice_driver_cur
Into @.L_driverid

Continue

End
Close invoice_driver_cur
DeAllocate invoice_driver_cur

Exec (@.C)
--EXEC sp_executesql @.C
GO

/**********************************************/

/*Sub Procedure sp_invoice_driver2 */

/**********************************************/

CREATE PROC sp_invoice_driver2 @.args NVarchar(1000), @.prev_month
NVarchar(100),
@.sqlstmt Varchar(8000) Output
AS

SET QUOTED_IDENTIFIER ON

SET @.sqlstmt = ' Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = ''' + Cast(@.args As NVARCHAR(20)) + '''
and
fee.currentmonth = ''' + Cast(@.prev_month As NVARCHAR(12)) +' '''

GO

/**********************************************/

This is what the Print Statement give:

/**********************************************/

Create View custom_invoice As Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September ' Union Select 2,
(SELECT Drivers.Description),
(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))
/ 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September' and
Drivers.Driversid = 3 Union Select 3,
(Select Drivers.Description From Drivers Where DriversID = 4),
Count(*) * Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 4 )As decimal(6,2)) / 12,
(Select Drivers.Currency
From Drivers Where DriversID = 4)
From Fund Union Select 4,
(Select Drivers.Description From Drivers Where DriversID = 5),
(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +
BillingReport.Man_Reg_Transexch +
BillingReport.Man_Allo_Purch +
BillingReport.Man_Allo_Red +
BillingReport.Man_Allo_Transexch +
BillingReport.Man_Allo_Adj_Purch +
BillingReport.Man_Allo_Adj_Red +
BillingReport.Man_Allo_Adj_Transexch +
BillingReport.Man_Adj_Purch +
BillingReport.Man_Adj_Red +
BillingReport.Man_Adj_Transexch ) ) +
(Select Sum(Cast(satuscnt As int ))
From Awd_stub
Where CurrentMonth = 'September'))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 5 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 5)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 5,
(Select Drivers.Description From Drivers Where DriversID = 6),
( Sum( BillingReport.Auto_Reg_Purch +
BillingReport.Auto_Reg_Red +
BillingReport.Auto_Reg_TRansexch +
BillingReport.Auto_Allo_Purch+
BillingReport.Auto_Allo_Red +
BillingReport.Auto_Allo_Transexch+
BillingReport.Auto_Allo_Adj_Purch+
BillingReport.Auto_Allo_Adj_Red+
BillingReport.Auto_Allo_Adj_transexch+
BillingReport.Auto_Adj_Purch+
BillingReport.Auto_Adj_Red+
BillingReport.Auto_Adj_Transexch )+
(Select Sum(Cast(Processed_msg As Int))
From XML_messaging
Where CurrentMonth = 'September'))*
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 6 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 6)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 6,
(Select Drivers.Description From Drivers Where DriversID = 7),
( ( a.Accountholder_Active_Accounts -
(select accountholder_active_accounts from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ) )
+
( a.Accountholder_Zero_Balance -
(select accountholder_zero_balance from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 7 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 7)
FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,
(Select Drivers.Description From Drivers Where DriversID = 8),
( Select telephone From cfxbill Where currentmonth = 'September') *
(Cast((select fe

Thanks for any help
MDThis is the worst use of SQL I have ever seen in 18 years of
programming in the language. If I put this in my next book, nobody
would believe it was real.

>> I am trying to create a dynamic SQL statement to create a view. <<

Which completely defeats the purpose of a VIEW and does it by using
the worst possible approach for production code. Dynamic SQL says that
you have no idea what your own schema should look like, so you assume
that any random user sometime in the future is going to do a better
job.

>> I have a stored procedure, which based on the parameters passed
calls different stored procedures. Each of this sub stored procedure
creates a string of custom SQL statement and returns this string back
to the main stored procedure. <<

Ever have a course in Software Engineering? Obviously not. I cannot
take the time to go over all the probldms with this approach; just get
a book on SE and read it. This has nothing to do with SQL per se, but
with the very **basics** of your trade.

I tried for almost an hour to just read and understand your code. How
do you expect anyone to maintain it? Your inconsistent
capitalization, failure to use alias table names and use of tabs in
the code was also a pain to the reader.

>> If I have more that 6 SQL statements concated then the main SQL
gets cut off. It doesn't matter in what sequence I create the main
SQL. <<

You are probably generating so much crappy code that you are
overflowing the limits of SQL Server.

Did you know that T-SQL was never meant to be an application
development language? As best I can figure out, this nightmare is a
UNION-ed mess of unrelated reports. Break it apart into VIEWs --
**real** VIEWs that are part of the schema, and not this
conglomeration of confused reports. Get rid of the hard-wired date;
if you want to write a stored procedure, you can make it a parameter.
Get rid of the string month name; in a tiered architecture, display is
done in the front end, not the database (again, that has nothign to do
with SQL per se, but is just a basic progrmaming principle).

Try things more like this:

CREATE VIEW Driver_2_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description,
B1.from_date,
(B1.active_accounts * F1.fee_rate)/12.00,
D1.currency
FROM BillingReports AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 2
AND D1.driver_id = 2;

But did you notice that "BillingReports" is CROSS JOIN-ed? When you
use the VIEW, you apply a from_date to it to get the range you want.

and so on for the other UNION-ed queries.

CREATE VIEW Driver_3_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description, B1.from_date,
B1.zero_balance * F1.fee_rate) / 12.00,
D1.currency
FROM BillingReport AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 3
AND D1.driver_id = 3;

Etc.

If this is how you are writing queries, it is a pretty good bet that
the DDL is also a nightmare of bad datatypes, lack of constraints and
so forth.|||jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0410201422.21deda06@.posting.google.com>...
> This is the worst use of SQL I have ever seen in 18 years of
> programming in the language. If I put this in my next book, nobody
> would believe it was real.
> >> I am trying to create a dynamic SQL statement to create a view. <<
> Which completely defeats the purpose of a VIEW and does it by using
> the worst possible approach for production code. Dynamic SQL says that
> you have no idea what your own schema should look like, so you assume
> that any random user sometime in the future is going to do a better
> job.
> >> I have a stored procedure, which based on the parameters passed
> calls different stored procedures. Each of this sub stored procedure
> creates a string of custom SQL statement and returns this string back
> to the main stored procedure. <<
> Ever have a course in Software Engineering? Obviously not. I cannot
> take the time to go over all the probldms with this approach; just get
> a book on SE and read it. This has nothing to do with SQL per se, but
> with the very **basics** of your trade.
> I tried for almost an hour to just read and understand your code. How
> do you expect anyone to maintain it? Your inconsistent
> capitalization, failure to use alias table names and use of tabs in
> the code was also a pain to the reader.
> >> If I have more that 6 SQL statements concated then the main SQL
> gets cut off. It doesn't matter in what sequence I create the main
> SQL. <<
> You are probably generating so much crappy code that you are
> overflowing the limits of SQL Server.
> Did you know that T-SQL was never meant to be an application
> development language? As best I can figure out, this nightmare is a
> UNION-ed mess of unrelated reports. Break it apart into VIEWs --
> **real** VIEWs that are part of the schema, and not this
> conglomeration of confused reports. Get rid of the hard-wired date;
> if you want to write a stored procedure, you can make it a parameter.
> Get rid of the string month name; in a tiered architecture, display is
> done in the front end, not the database (again, that has nothign to do
> with SQL per se, but is just a basic progrmaming principle).
> Try things more like this:
> CREATE VIEW Driver_2_invoice (description, from_date, amount,
> currency)
> AS
> SELECT D1.description,
> B1.from_date,
> (B1.active_accounts * F1.fee_rate)/12.00,
> D1.currency
> FROM BillingReports AS B1, Drivers AS D1, Fee AS F1
> WHERE F1.driver_id = 2
> AND D1.driver_id = 2;
> But did you notice that "BillingReports" is CROSS JOIN-ed? When you
> use the VIEW, you apply a from_date to it to get the range you want.
> and so on for the other UNION-ed queries.
> CREATE VIEW Driver_3_invoice (description, from_date, amount,
> currency)
> AS
> SELECT D1.description, B1.from_date,
> B1.zero_balance * F1.fee_rate) / 12.00,
> D1.currency
> FROM BillingReport AS B1, Drivers AS D1, Fee AS F1
> WHERE F1.driver_id = 3
> AND D1.driver_id = 3;
> Etc.
> If this is how you are writing queries, it is a pretty good bet that
> the DDL is also a nightmare of bad datatypes, lack of constraints and
> so forth.

Thank you for your comments. I will get a book on SE and follow some
of your pointer. I understand that you have written a numerous books
on SQL but surely there is not need to be rude! I have overlooked the
alias part but without even knowing what my user requirements are try
not to pass judgement. Again thank you for your feed back considering
that you had to waste 1 hr going through my code. For sure I am going
to buy one of your books, which one would you recommend?|||>> I will get a book on SE and follow some
of your pointer. <<

The classics are by Yourdon, DeMacro and Constantine. I also like Gane
& Sarson for systems level stuff -- IST is a better diagramming method
than Yourdon.

>> I understand that you have written a numerous books
on SQL but surely there is not need to be rude! <<

My wife is the fukatan at a Zen monastary; they beat their students with
sticks :)

>> For sure I am going to buy one of your books, which one would you
recommend? <<

SQL FOR SMARTIES is the classic that most SQL programmer have on their
desk with post-it notes sticking out of it. It is a collection of SQL
programming techniques. I will start work on the third edition next
month, but I have no idea when it will come out; certainly not until
2005.

my DATA & DATABASES is a good look at foundations and some hueristics
for database in general. Pay attention to scales and measurements and
the design of encoding schemes; I seem to be the only guy who talks
about how to actually design data representations as opposed to
databases.

Terry Halpin's ORM book is great for data modeling.

if you want a mental workout to see if you are getting the idea of
thinking in sets, I also have a SQL PUZZLES & ANSWERS book. Sales were
lousy, but teachers keep using it for homework assignments.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||[posted and mailed, please reply in news]

MD (mdaptardar@.ifdsgroup.com) writes:
> I am trying to create a dynamic SQL statement to create a view.
> I have a stored procedure, which based on the parameters passed calls
> different stored procedures. Each of this sub stored procedure creates
> a string of custom SQL statement and returns this string back to the
> main stored procedure.
> This SQL statements work fine on there own. The SQL returned from the
> sub stored procedure are returned fine. The datatype of the variable
> that this sql is stored in Varchar(I have tried using nvarchar also
> same problem).
> If I have more that 6 SQL statements concated then the main SQL gets
> cut off. It doesnt matter in what sequence I create the main SQL.

Supposedly the SQL statement by then exceeds 8000 characters.

The usual remedy is to have more than one SQL variable:

EXEC(@.sql1 + @.sql2 + @.sql3 + ...)

But since you are in a loop, this is not possible. In the next version of
SQL Server, currently in Beta, there is a simple solution: use the new
varchar(MAX) datatype. Here you can fit in 2GB of SQL. Alas, in SQL 2000
you only have the text data type which you cannot assign to.

But there is a solution: insert all the SQL Segments into a temp table:

CREATE TABLE #sql(id int IDENTITY, sql varchar(8000) NOT NULL)

Now loop over this table and for each row append to two variables:

SELECT @.a = '' -- Init
SELECT @.b = 'EXEC('
...
SELECT @.a = @.a + 'DELCARE @.sql' + ltrim(str(id)) + ' varchar(8000)
SELECT @.sql = ' + sql,
@.b = @.b + '@.sql' + ltrim(str(id)) + ' + '
FROM #sql
WHERE id = @.id
...
-- Final
SELECT @.b = @.b + '')'

EXEC(@.a + @.b)

That is @.a + @.b builds a an batch that in its turn calls EXEC() to create
your view.

I hope that you by now realise that you have all reason to reconsider your
design.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp