I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||I am using a table function with the following syntax:
create function dbo.TestFunction (@.InputVariable int)
returns table
with encryption
as
declare @.LocalVariable smalldatetime
select @.LocalVariable = ABCDate from ABCTable where rowid = @.InputVariable
return(
select * from XYZTable where XYZDate < @.LocalVariable
)
Any thoughts?
Originally posted by rdjabarov
You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.|||gtocha...thanks
Originally posted by rdjabarov
It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.
Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts
Friday, March 9, 2012
Local variables in stored procedures
Hi!
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>
Local variables in stored proc
How do you declare and then SELECT a value for a local variable within
stored procedure, increment the value and then use in an Insert
statement? Thanks
stored procedure, increment the value and then use in an Insert
statement? Thanks
Any sites that explain this syntax for SQL Server 2000? Thanks
hals_left
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint,
// this needs to be a local var not an output param, how ?
@.UnitPosition tinyint Output
AS
// Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef
// inc the value
@.UnitPosition+=1
// Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)
GO-- MODIFIED STORED PROC:
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint
AS
-- this needs to be a local var not an output param, how ?
declare @.UnitPosition tinyint
-- Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= MAX(UnitPosition)
FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef
-- inc the value
select @.UnitPosition=@.UnitPosition+1
-- Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)|||See below and the following link.
http://www.google.co.uk/groups?selm...%40giganews.com
But your proc looks a bit strange. Why not just make the key (qualref,
unitref, unitgroupref) and then increment a quantity column? Like:
UPDATE tblQualUnits
SET unit_quantity = unit_quantity + 1
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
Otherwise your table is just an accumulator of redundant rows.
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.qualref TINYINT,
@.unitref TINYINT,
@.unitgroupref TINYINT
AS
INSERT INTO tblQualUnits (qualref, unitref, unitgroupref, unitposition)
SELECT @.qualref, @.unitref, @.unitgroupref,
COALESCE(MAX(unitposition),0)+1
FROM tblUnitGroup
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
GO
--
David Portas
SQL Server MVP
--|||Thankyou for the quick reply.|||Thanks
Local Temp tables versus Global
I have a SP that dynamicly constructs a sql phrase that is executed with the
execute keyword correctly.
One of the variables is the name of a Global temporary table created on the
fly as follows
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
Then I execute the following
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
I had some concurrency issues and wanted to switch to Local Temporary Table,
so as soon as I take off one of the '#' signs, execute ther SP I get the
following error :
---
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#CCOI154315_61466018841ML160'.
----
When re-putting the second # sign, the SP works like a charm
Al I missing something regarding local temporary tables
Thanks in advanceHi
The creation of the table is not in the same scope as the executed SELECT
statement. You may want to execute them together.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>I have a SP that dynamicly constructs a sql phrase that is executed with
>the
> execute keyword correctly.
> One of the variables is the name of a Global temporary table created on
> the
> fly as follows
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
> Then I execute the following
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
>
> I had some concurrency issues and wanted to switch to Local Temporary
> Table,
> so as soon as I take off one of the '#' signs, execute ther SP I get the
> following error :
> ---
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#CCOI154315_61466018841ML160'.
> ----
> When re-putting the second # sign, the SP works like a charm
> Al I missing something regarding local temporary tables
> Thanks in advance|||Thansk for your response. As I said they are in the same SP.
Can you please explain what do you mean by ' not in the same scope '.
As I understand from what is indicated in BOL that nested SP in SP can call
local temporary tables :
--
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
--
My understanding is that second level sp can execute sql phrases against LTT
in hosting SP, No?
"John Bell" wrote:
> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||One last thing I forgot to add, how do you execute both in the same scope?
can youn please show me?
Thansk in advance
"John Bell" wrote:
> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||Hi
I used scope instead of session. You can use the temporary tables in called
procedures but you can't create the temporary table in one sub-procedure and
then use it in the next it has to be created at the higher level.
If you posted and example that works when you use a global temporary table
and fails when local it may help.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
> Thansk for your response. As I said they are in the same SP.
> Can you please explain what do you mean by ' not in the same scope '.
> As I understand from what is indicated in BOL that nested SP in SP can
> call
> local temporary tables :
> --
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored
> procedure
> that created the table. The table cannot be referenced by the process
> which
> called the stored procedure that created the table.
> --
> My understanding is that second level sp can execute sql phrases against
> LTT
> in hosting SP, No?
> "John Bell" wrote:
>|||here we go
Here is 2 SPs, thje first one woth 2 '#' signe the second with one
I provided a create table which you can insert the following item
"3C17203-ME" in for test
purposes
For testing the SPs call them as follows (I have taken a lot of info and
joins from the original one)
bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
The table used by the simplified PROC
CREATE TABLE [dbo].[PRODUCT_NG] (
[PROD_MANF_SKU] [varchar] (20)
) ON [PRIMARY]
GO
SP with 2Signs
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
The single sign proc
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
"John Bell" wrote:
> Hi
> I used scope instead of session. You can use the temporary tables in calle
d
> procedures but you can't create the temporary table in one sub-procedure a
nd
> then use it in the next it has to be created at the higher level.
> If you posted and example that works when you use a global temporary table
> and fails when local it may help.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
>
>|||Hi
This may wrap but try:
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = ' FROM dbo.PRODUCT_NG '
Set @.sWhereStart = ' '
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
--Rtrim(@.sSQL)
EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU] [varchar]
(20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName + ' (
[fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' + @.sFromWhereStart
+ ' DROP TABLE ' + @.lstr_sTableName + ' END')
GO
The BEGIN and END show the scope for the temporary table.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
> here we go
> Here is 2 SPs, thje first one woth 2 '#' signe the second with one
> I provided a create table which you can insert the following item
> "3C17203-ME" in for test
> purposes
> For testing the SPs call them as follows (I have taken a lot of info and
> joins from the original one)
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
>
> The table used by the simplified PROC
> CREATE TABLE [dbo].[PRODUCT_NG] (
> [PROD_MANF_SKU] [varchar] (20)
> ) ON [PRIMARY]
> GO
>
> SP with 2Signs
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> The single sign proc
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> "John Bell" wrote:
>|||Hi
To add...
There should be no need to worry about creating a unique name as this will
be done for you. This would then allow you to create the temporary table as
normal e.g.
CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
''def''')
SELECT * FROM #tmp1
DROP TABLE #tmp1
Your contention may be from using SELECT ... INTO as this may lock the
system tables in tempdb for a prolonged period. This was certainly an issue
on older versions of SQL Server and is a good practice to avoid.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
> Hi
> This may wrap but try:
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = ' FROM dbo.PRODUCT_NG '
> Set @.sWhereStart = ' '
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> --Rtrim(@.sSQL)
> EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU]
> [varchar] (20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName +
> ' ( [fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' +
> @.sFromWhereStart + ' DROP TABLE ' + @.lstr_sTableName + ' END')
> GO
> The BEGIN and END show the scope for the temporary table.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
>|||So many thanks for your suggestion. As I said I have taken a lot of the T-SQ
L
off to make it simple.
The 'Select Into.....' phrase is followed by several dynamic sql as follows
EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
So If I follow your first suggestion it means I shoyuld concatenate a ton of
sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
debugging.
Of course the seond suggestion can not be used because it is straight T-SQL
and not dynamic
I would appreciate any other ideas or fixes if possible of course.
Salam
"John Bell" wrote:
> Hi
> To add...
> There should be no need to worry about creating a unique name as this will
> be done for you. This would then allow you to create the temporary table a
s
> normal e.g.
> CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
> INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
> ''def''')
> SELECT * FROM #tmp1
> DROP TABLE #tmp1
> Your contention may be from using SELECT ... INTO as this may lock the
> system tables in tempdb for a prolonged period. This was certainly an issu
e
> on older versions of SQL Server and is a good practice to avoid.
> John
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi
The second suggestion is that the table is static but the SQL is dynamic, I
have never found a application that really required dynamic table
definitions. You may want to re-consider the design of the process and the
reasons you think it has to be dynamic.
You may also want to read http://www.sommarskog.se/dyn-search.html to see if
there is anything that can be used.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2D75B7F3-930F-4E6D-8255-56C05E898E97@.microsoft.com...
> So many thanks for your suggestion. As I said I have taken a lot of the
> T-SQL
> off to make it simple.
> The 'Select Into.....' phrase is followed by several dynamic sql as
> follows
> EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
> So If I follow your first suggestion it means I shoyuld concatenate a ton
> of
> sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
> debugging.
> Of course the seond suggestion can not be used because it is straight
> T-SQL
> and not dynamic
> I would appreciate any other ideas or fixes if possible of course.
> Salam
> "John Bell" wrote:
>
execute keyword correctly.
One of the variables is the name of a Global temporary table created on the
fly as follows
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
Then I execute the following
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
I had some concurrency issues and wanted to switch to Local Temporary Table,
so as soon as I take off one of the '#' signs, execute ther SP I get the
following error :
---
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#CCOI154315_61466018841ML160'.
----
When re-putting the second # sign, the SP works like a charm
Al I missing something regarding local temporary tables
Thanks in advanceHi
The creation of the table is not in the same scope as the executed SELECT
statement. You may want to execute them together.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>I have a SP that dynamicly constructs a sql phrase that is executed with
>the
> execute keyword correctly.
> One of the variables is the name of a Global temporary table created on
> the
> fly as follows
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
> Then I execute the following
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
>
> I had some concurrency issues and wanted to switch to Local Temporary
> Table,
> so as soon as I take off one of the '#' signs, execute ther SP I get the
> following error :
> ---
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#CCOI154315_61466018841ML160'.
> ----
> When re-putting the second # sign, the SP works like a charm
> Al I missing something regarding local temporary tables
> Thanks in advance|||Thansk for your response. As I said they are in the same SP.
Can you please explain what do you mean by ' not in the same scope '.
As I understand from what is indicated in BOL that nested SP in SP can call
local temporary tables :
--
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
--
My understanding is that second level sp can execute sql phrases against LTT
in hosting SP, No?
"John Bell" wrote:
> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||One last thing I forgot to add, how do you execute both in the same scope?
can youn please show me?
Thansk in advance
"John Bell" wrote:
> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||Hi
I used scope instead of session. You can use the temporary tables in called
procedures but you can't create the temporary table in one sub-procedure and
then use it in the next it has to be created at the higher level.
If you posted and example that works when you use a global temporary table
and fails when local it may help.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
> Thansk for your response. As I said they are in the same SP.
> Can you please explain what do you mean by ' not in the same scope '.
> As I understand from what is indicated in BOL that nested SP in SP can
> call
> local temporary tables :
> --
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored
> procedure
> that created the table. The table cannot be referenced by the process
> which
> called the stored procedure that created the table.
> --
> My understanding is that second level sp can execute sql phrases against
> LTT
> in hosting SP, No?
> "John Bell" wrote:
>|||here we go
Here is 2 SPs, thje first one woth 2 '#' signe the second with one
I provided a create table which you can insert the following item
"3C17203-ME" in for test
purposes
For testing the SPs call them as follows (I have taken a lot of info and
joins from the original one)
bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
The table used by the simplified PROC
CREATE TABLE [dbo].[PRODUCT_NG] (
[PROD_MANF_SKU] [varchar] (20)
) ON [PRIMARY]
GO
SP with 2Signs
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
The single sign proc
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
"John Bell" wrote:
> Hi
> I used scope instead of session. You can use the temporary tables in calle
d
> procedures but you can't create the temporary table in one sub-procedure a
nd
> then use it in the next it has to be created at the higher level.
> If you posted and example that works when you use a global temporary table
> and fails when local it may help.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
>
>|||Hi
This may wrap but try:
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = ' FROM dbo.PRODUCT_NG '
Set @.sWhereStart = ' '
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
--Rtrim(@.sSQL)
EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU] [varchar]
(20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName + ' (
[fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' + @.sFromWhereStart
+ ' DROP TABLE ' + @.lstr_sTableName + ' END')
GO
The BEGIN and END show the scope for the temporary table.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
> here we go
> Here is 2 SPs, thje first one woth 2 '#' signe the second with one
> I provided a create table which you can insert the following item
> "3C17203-ME" in for test
> purposes
> For testing the SPs call them as follows (I have taken a lot of info and
> joins from the original one)
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
>
> The table used by the simplified PROC
> CREATE TABLE [dbo].[PRODUCT_NG] (
> [PROD_MANF_SKU] [varchar] (20)
> ) ON [PRIMARY]
> GO
>
> SP with 2Signs
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> The single sign proc
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> "John Bell" wrote:
>|||Hi
To add...
There should be no need to worry about creating a unique name as this will
be done for you. This would then allow you to create the temporary table as
normal e.g.
CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
''def''')
SELECT * FROM #tmp1
DROP TABLE #tmp1
Your contention may be from using SELECT ... INTO as this may lock the
system tables in tempdb for a prolonged period. This was certainly an issue
on older versions of SQL Server and is a good practice to avoid.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
> Hi
> This may wrap but try:
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = ' FROM dbo.PRODUCT_NG '
> Set @.sWhereStart = ' '
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> --Rtrim(@.sSQL)
> EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU]
> [varchar] (20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName +
> ' ( [fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' +
> @.sFromWhereStart + ' DROP TABLE ' + @.lstr_sTableName + ' END')
> GO
> The BEGIN and END show the scope for the temporary table.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
>|||So many thanks for your suggestion. As I said I have taken a lot of the T-SQ
L
off to make it simple.
The 'Select Into.....' phrase is followed by several dynamic sql as follows
EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
So If I follow your first suggestion it means I shoyuld concatenate a ton of
sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
debugging.
Of course the seond suggestion can not be used because it is straight T-SQL
and not dynamic
I would appreciate any other ideas or fixes if possible of course.
Salam
"John Bell" wrote:
> Hi
> To add...
> There should be no need to worry about creating a unique name as this will
> be done for you. This would then allow you to create the temporary table a
s
> normal e.g.
> CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
> INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
> ''def''')
> SELECT * FROM #tmp1
> DROP TABLE #tmp1
> Your contention may be from using SELECT ... INTO as this may lock the
> system tables in tempdb for a prolonged period. This was certainly an issu
e
> on older versions of SQL Server and is a good practice to avoid.
> John
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi
The second suggestion is that the table is static but the SQL is dynamic, I
have never found a application that really required dynamic table
definitions. You may want to re-consider the design of the process and the
reasons you think it has to be dynamic.
You may also want to read http://www.sommarskog.se/dyn-search.html to see if
there is anything that can be used.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2D75B7F3-930F-4E6D-8255-56C05E898E97@.microsoft.com...
> So many thanks for your suggestion. As I said I have taken a lot of the
> T-SQL
> off to make it simple.
> The 'Select Into.....' phrase is followed by several dynamic sql as
> follows
> EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
> So If I follow your first suggestion it means I shoyuld concatenate a ton
> of
> sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
> debugging.
> Of course the seond suggestion can not be used because it is straight
> T-SQL
> and not dynamic
> I would appreciate any other ideas or fixes if possible of course.
> Salam
> "John Bell" wrote:
>
Subscribe to:
Posts (Atom)