Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.
Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>
|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:[vbcol=seagreen]
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessary
> to pass the table name. For more on temporary tables read Books Online or at
> http://msdn.microsoft.com/library/de...eate2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure that
> tempdb is on it's own discs. Without knowing more about what you are trying
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Friday, March 23, 2012
Potential Concurrency Issue?
Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/d... />
2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:[vbcol=seagreen]
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessa
ry
> to pass the table name. For more on temporary tables read Books Online or
at
> http://msdn.microsoft.com/library/d...>
te2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure th
at
> tempdb is on it's own discs. Without knowing more about what you are tryin
g
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
>|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:
> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/d...
a-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
>|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:
> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
----
----
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
--
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
--
John Bell wrote:[vbcol=seagreen]
> Hi
> Can you give an example SQL Statement and the values in the table that wil
l
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
>|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:
> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ----
----
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
--
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
--
>
>
> John Bell wrote:
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will
be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you ha
ve
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
>|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if yo
u
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:
> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
>
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/d... />
2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:[vbcol=seagreen]
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessa
ry
> to pass the table name. For more on temporary tables read Books Online or
at
> http://msdn.microsoft.com/library/d...>
te2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure th
at
> tempdb is on it's own discs. Without knowing more about what you are tryin
g
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
>|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:
> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/d...
a-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
>|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:
> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
----
----
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
--
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
--
John Bell wrote:[vbcol=seagreen]
> Hi
> Can you give an example SQL Statement and the values in the table that wil
l
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
>|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:
> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ----
----
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
--
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
--
>
>
> John Bell wrote:
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will
be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you ha
ve
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
>|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if yo
u
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:
> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
>
Potential Concurrency Issue?
Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessary
> to pass the table name. For more on temporary tables read Books Online or at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure that
> tempdb is on it's own discs. Without knowing more about what you are trying
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
> > Hi,
> > I want to know if the below mentioned pseudo code can pose a
> > concurrency problem.
> >
> > declare @.string
> >
> > CREATE TABLE #temp
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> > --INSERT some value
> >
> > --Call another stored procedure that takes the table name and a string
> >
> > EXEC usp_replace '#temp',@.string OUTPUT
> >
> > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > entries in #temp
> >
> >
> >
> > Now the question is that since im passing the table name and using it
> > to query in the usp_replace could this mess up the values if multiple
> > concurrent users execute it?
> >
> > Would really appreciate any thoughts on this. Thanks.
> >
> >|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:
> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
> > Hi
> >
> > If you have created the temporary table at the outer level it will be
> > available to the inner level procedure, therefore it should not be necessary
> > to pass the table name. For more on temporary tables read Books Online or at
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> >
> > If many processes are creating/dropping temporary tables you can get
> > contention on tempdb, this can be limited to some degree by making sure that
> > tempdb is on it's own discs. Without knowing more about what you are trying
> > to achieve it is not possible to suggest an alternative approach.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > I want to know if the below mentioned pseudo code can pose a
> > > concurrency problem.
> > >
> > > declare @.string
> > >
> > > CREATE TABLE #temp
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > > --INSERT some value
> > >
> > > --Call another stored procedure that takes the table name and a string
> > >
> > > EXEC usp_replace '#temp',@.string OUTPUT
> > >
> > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > entries in #temp
> > >
> > >
> > >
> > > Now the question is that since im passing the table name and using it
> > > to query in the usp_replace could this mess up the values if multiple
> > > concurrent users execute it?
> > >
> > > Would really appreciate any thoughts on this. Thanks.
> > >
> > >
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
> > Thanks John!
> > to be more precise on what im tryin to achieve is :
> >
> > To create a generic stored procedure that replace the occurence of
> > certain "place holders" in a string.
> > The placeholders' values are stored in a table as a name value pair.
> > This table can be either generates on the fly as a temp table or be a
> > permanent table.
> >
> > e.g. the string is '<name> who is <age> years old lives in <city>'
> >
> > the temp table will be
> > name | value
> > --
> > <name> | jack
> > <age> | 23
> > <city> | NY
> >
> > appreciate your help, thanks!
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If you have created the temporary table at the outer level it will be
> > > available to the inner level procedure, therefore it should not be necessary
> > > to pass the table name. For more on temporary tables read Books Online or at
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > >
> > > If many processes are creating/dropping temporary tables you can get
> > > contention on tempdb, this can be limited to some degree by making sure that
> > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > to achieve it is not possible to suggest an alternative approach.
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > I want to know if the below mentioned pseudo code can pose a
> > > > concurrency problem.
> > > >
> > > > declare @.string
> > > >
> > > > CREATE TABLE #temp
> > > > (
> > > > name VARCHAR(100),
> > > > value VARCHAR(100)
> > > > )
> > > > --INSERT some value
> > > >
> > > > --Call another stored procedure that takes the table name and a string
> > > >
> > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > >
> > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > entries in #temp
> > > >
> > > >
> > > >
> > > > Now the question is that since im passing the table name and using it
> > > > to query in the usp_replace could this mess up the values if multiple
> > > > concurrent users execute it?
> > > >
> > > > Would really appreciate any thoughts on this. Thanks.
> > > >
> > > >
> >
> >|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:
> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > If these are just values then you should be able to join to this table and
> > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > e.g. column names then
> > see
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > but still pass variables for the values. You may want to look at
> > http://www.sommarskog.se/dynamic_sql.html and
> > http://www.sommarskog.se/dyn-search.html
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Thanks John!
> > > to be more precise on what im tryin to achieve is :
> > >
> > > To create a generic stored procedure that replace the occurence of
> > > certain "place holders" in a string.
> > > The placeholders' values are stored in a table as a name value pair.
> > > This table can be either generates on the fly as a temp table or be a
> > > permanent table.
> > >
> > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > >
> > > the temp table will be
> > > name | value
> > > --
> > > <name> | jack
> > > <age> | 23
> > > <city> | NY
> > >
> > > appreciate your help, thanks!
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If you have created the temporary table at the outer level it will be
> > > > available to the inner level procedure, therefore it should not be necessary
> > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > >
> > > > If many processes are creating/dropping temporary tables you can get
> > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > to achieve it is not possible to suggest an alternative approach.
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > concurrency problem.
> > > > >
> > > > > declare @.string
> > > > >
> > > > > CREATE TABLE #temp
> > > > > (
> > > > > name VARCHAR(100),
> > > > > value VARCHAR(100)
> > > > > )
> > > > > --INSERT some value
> > > > >
> > > > > --Call another stored procedure that takes the table name and a string
> > > > >
> > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > >
> > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > entries in #temp
> > > > >
> > > > >
> > > > >
> > > > > Now the question is that since im passing the table name and using it
> > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > concurrent users execute it?
> > > > >
> > > > > Would really appreciate any thoughts on this. Thanks.
> > > > >
> > > > >
> > >
> > >
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
------
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
John Bell wrote:
> Hi
> Can you give an example SQL Statement and the values in the table that will
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
> > Hi,
> > the dynamic sql thing looks great, but i dont know how it will
> > rescue me as a n aternative in this case. could you please throw some
> > more light on this.
> > probably my last post wasnt very clear, so the deal is:
> >
> > i have a table which has name value pairs.
> > i have a string which has names as place holders for the values
> > in the string i need to replace those names with the values as found in
> > the table.
> > i want a general reusable stored proc to acheive this.
> > the way i am doing it as desribed in the first post works, but i want
> > to know if that will pose any concurrency problems.
> > since i want the sp to general and reusable i dont want to hard code
> > and use the table name in the usp_replace sp.
> >
> > Please let me know your thoughts.
> > Thanks,
> > Rishi.
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If these are just values then you should be able to join to this table and
> > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > e.g. column names then
> > > see
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > but still pass variables for the values. You may want to look at
> > > http://www.sommarskog.se/dynamic_sql.html and
> > > http://www.sommarskog.se/dyn-search.html
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Thanks John!
> > > > to be more precise on what im tryin to achieve is :
> > > >
> > > > To create a generic stored procedure that replace the occurence of
> > > > certain "place holders" in a string.
> > > > The placeholders' values are stored in a table as a name value pair.
> > > > This table can be either generates on the fly as a temp table or be a
> > > > permanent table.
> > > >
> > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > >
> > > > the temp table will be
> > > > name | value
> > > > --
> > > > <name> | jack
> > > > <age> | 23
> > > > <city> | NY
> > > >
> > > > appreciate your help, thanks!
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If you have created the temporary table at the outer level it will be
> > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > >
> > > > > If many processes are creating/dropping temporary tables you can get
> > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > to achieve it is not possible to suggest an alternative approach.
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Hi,
> > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > concurrency problem.
> > > > > >
> > > > > > declare @.string
> > > > > >
> > > > > > CREATE TABLE #temp
> > > > > > (
> > > > > > name VARCHAR(100),
> > > > > > value VARCHAR(100)
> > > > > > )
> > > > > > --INSERT some value
> > > > > >
> > > > > > --Call another stored procedure that takes the table name and a string
> > > > > >
> > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > >
> > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > entries in #temp
> > > > > >
> > > > > >
> > > > > >
> > > > > > Now the question is that since im passing the table name and using it
> > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > concurrent users execute it?
> > > > > >
> > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:
> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ------
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
>
>
> John Bell wrote:
> > Hi
> >
> > Can you give an example SQL Statement and the values in the table that will
> > be used and the code to usp_replace?
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > the dynamic sql thing looks great, but i dont know how it will
> > > rescue me as a n aternative in this case. could you please throw some
> > > more light on this.
> > > probably my last post wasnt very clear, so the deal is:
> > >
> > > i have a table which has name value pairs.
> > > i have a string which has names as place holders for the values
> > > in the string i need to replace those names with the values as found in
> > > the table.
> > > i want a general reusable stored proc to acheive this.
> > > the way i am doing it as desribed in the first post works, but i want
> > > to know if that will pose any concurrency problems.
> > > since i want the sp to general and reusable i dont want to hard code
> > > and use the table name in the usp_replace sp.
> > >
> > > Please let me know your thoughts.
> > > Thanks,
> > > Rishi.
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If these are just values then you should be able to join to this table and
> > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > e.g. column names then
> > > > see
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > but still pass variables for the values. You may want to look at
> > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > http://www.sommarskog.se/dyn-search.html
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Thanks John!
> > > > > to be more precise on what im tryin to achieve is :
> > > > >
> > > > > To create a generic stored procedure that replace the occurence of
> > > > > certain "place holders" in a string.
> > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > This table can be either generates on the fly as a temp table or be a
> > > > > permanent table.
> > > > >
> > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > >
> > > > > the temp table will be
> > > > > name | value
> > > > > --
> > > > > <name> | jack
> > > > > <age> | 23
> > > > > <city> | NY
> > > > >
> > > > > appreciate your help, thanks!
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If you have created the temporary table at the outer level it will be
> > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > >
> > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > concurrency problem.
> > > > > > >
> > > > > > > declare @.string
> > > > > > >
> > > > > > > CREATE TABLE #temp
> > > > > > > (
> > > > > > > name VARCHAR(100),
> > > > > > > value VARCHAR(100)
> > > > > > > )
> > > > > > > --INSERT some value
> > > > > > >
> > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > >
> > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > >
> > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > entries in #temp
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > concurrent users execute it?
> > > > > > >
> > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you have
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
> > Sure John! here it is:
> >
> >
> > --- in main stored proc
> > ----
> >
> > --Some code goes here
> >
> > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > <property1>'
> >
> > CREATE TABLE #prop
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> >
> > -- Get Standard Attributes
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> >
> > -- Get user defined properties
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> > --
> > --
> > --
> >
> > EXEC usp_replace '#prop',@.string output
> >
> >
> >
> >
> > ------
> >
> > ---
> > usp_replace----
> >
> > ALTER PROCEDURE [dbo].[usp_replace]
> >
> > @.tablename VARCHAR(100) ,
> > @.string VARCHAR(1000) OUTPUT
> > AS
> > BEGIN
> > --IF(nullif(@.string,'') is null) RETURN
> >
> > DECLARE @.tableqry VARCHAR(100)
> > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> >
> > SET @.tableqry = 'SELECT * FROM '+@.tablename
> >
> > INSERT INTO @.temp
> > EXEC (@.tableqry)
> >
> > DECLARE c CURSOR FOR
> > SELECT * FROM @.temp
> >
> > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > OPEN c
> > FETCH next FROM c INTO @.name,@.value
> > WHILE @.@.fetch_status =0 BEGIN
> > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> >
> > FETCH next FROM c INTO @.name,@.value
> >
> > END
> > CLOSE c
> > DEALLOCATE c
> >
> >
> > END
> >
> > ---
> > usp_replace----
> >
> >
> > the table #prop looks like this after values are inserted in it in the
> > main sp.
> >
> > name | value
> > ---
> > <attribute1> | attributvalue
> > <attribute2 > | attribute2value
> > ----
> >
> >
> > finallly the string looks like this after returned from sp_replace
> >
> > ' attributvalue some text here attribute2value some text here
> > <property1>'
> > ----
> >
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Can you give an example SQL Statement and the values in the table that will
> > > be used and the code to usp_replace?
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > the dynamic sql thing looks great, but i dont know how it will
> > > > rescue me as a n aternative in this case. could you please throw some
> > > > more light on this.
> > > > probably my last post wasnt very clear, so the deal is:
> > > >
> > > > i have a table which has name value pairs.
> > > > i have a string which has names as place holders for the values
> > > > in the string i need to replace those names with the values as found in
> > > > the table.
> > > > i want a general reusable stored proc to acheive this.
> > > > the way i am doing it as desribed in the first post works, but i want
> > > > to know if that will pose any concurrency problems.
> > > > since i want the sp to general and reusable i dont want to hard code
> > > > and use the table name in the usp_replace sp.
> > > >
> > > > Please let me know your thoughts.
> > > > Thanks,
> > > > Rishi.
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If these are just values then you should be able to join to this table and
> > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > e.g. column names then
> > > > > see
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > but still pass variables for the values. You may want to look at
> > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > http://www.sommarskog.se/dyn-search.html
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Thanks John!
> > > > > > to be more precise on what im tryin to achieve is :
> > > > > >
> > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > certain "place holders" in a string.
> > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > permanent table.
> > > > > >
> > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > >
> > > > > > the temp table will be
> > > > > > name | value
> > > > > > --
> > > > > > <name> | jack
> > > > > > <age> | 23
> > > > > > <city> | NY
> > > > > >
> > > > > > appreciate your help, thanks!
> > > > > >
> > > > > >
> > > > > >
> > > > > > John Bell wrote:
> > > > > > > Hi
> > > > > > >
> > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > >
> > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Rishi" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > concurrency problem.
> > > > > > > >
> > > > > > > > declare @.string
> > > > > > > >
> > > > > > > > CREATE TABLE #temp
> > > > > > > > (
> > > > > > > > name VARCHAR(100),
> > > > > > > > value VARCHAR(100)
> > > > > > > > )
> > > > > > > > --INSERT some value
> > > > > > > >
> > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > >
> > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > >
> > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > entries in #temp
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > concurrent users execute it?
> > > > > > > >
> > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if you
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:
> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > As your strings with the placeholders are not SQL Statements then don't
> > think I can suggest a different alternative.
> >
> > I am not sure why you need to dynamic SQL for returning values (or to pass
> > the t able name) from your temporary table, unless the substitutions will be
> > embeded it should not be necessary! You may want to try ordering the
> > processing by the length of the value to try and avoid replacing already
> > substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> > table variable? For you string size you will may have truncation if you have
> > more than 9 substitutions.
> >
> > If this is just going to be sent to the client you may just want to do the
> > replacement on the client.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Sure John! here it is:
> > >
> > >
> > > --- in main stored proc
> > > ----
> > >
> > > --Some code goes here
> > >
> > > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > > <property1>'
> > >
> > > CREATE TABLE #prop
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > >
> > > -- Get Standard Attributes
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > >
> > > -- Get user defined properties
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > > --
> > > --
> > > --
> > >
> > > EXEC usp_replace '#prop',@.string output
> > >
> > >
> > >
> > >
> > > ------
> > >
> > > ---
> > > usp_replace----
> > >
> > > ALTER PROCEDURE [dbo].[usp_replace]
> > >
> > > @.tablename VARCHAR(100) ,
> > > @.string VARCHAR(1000) OUTPUT
> > > AS
> > > BEGIN
> > > --IF(nullif(@.string,'') is null) RETURN
> > >
> > > DECLARE @.tableqry VARCHAR(100)
> > > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> > >
> > > SET @.tableqry = 'SELECT * FROM '+@.tablename
> > >
> > > INSERT INTO @.temp
> > > EXEC (@.tableqry)
> > >
> > > DECLARE c CURSOR FOR
> > > SELECT * FROM @.temp
> > >
> > > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > > OPEN c
> > > FETCH next FROM c INTO @.name,@.value
> > > WHILE @.@.fetch_status =0 BEGIN
> > > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> > >
> > > FETCH next FROM c INTO @.name,@.value
> > >
> > > END
> > > CLOSE c
> > > DEALLOCATE c
> > >
> > >
> > > END
> > >
> > > ---
> > > usp_replace----
> > >
> > >
> > > the table #prop looks like this after values are inserted in it in the
> > > main sp.
> > >
> > > name | value
> > > ---
> > > <attribute1> | attributvalue
> > > <attribute2 > | attribute2value
> > > ----
> > >
> > >
> > > finallly the string looks like this after returned from sp_replace
> > >
> > > ' attributvalue some text here attribute2value some text here
> > > <property1>'
> > > ----
> > >
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Can you give an example SQL Statement and the values in the table that will
> > > > be used and the code to usp_replace?
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > the dynamic sql thing looks great, but i dont know how it will
> > > > > rescue me as a n aternative in this case. could you please throw some
> > > > > more light on this.
> > > > > probably my last post wasnt very clear, so the deal is:
> > > > >
> > > > > i have a table which has name value pairs.
> > > > > i have a string which has names as place holders for the values
> > > > > in the string i need to replace those names with the values as found in
> > > > > the table.
> > > > > i want a general reusable stored proc to acheive this.
> > > > > the way i am doing it as desribed in the first post works, but i want
> > > > > to know if that will pose any concurrency problems.
> > > > > since i want the sp to general and reusable i dont want to hard code
> > > > > and use the table name in the usp_replace sp.
> > > > >
> > > > > Please let me know your thoughts.
> > > > > Thanks,
> > > > > Rishi.
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If these are just values then you should be able to join to this table and
> > > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > > e.g. column names then
> > > > > > see
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > > but still pass variables for the values. You may want to look at
> > > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > > http://www.sommarskog.se/dyn-search.html
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Thanks John!
> > > > > > > to be more precise on what im tryin to achieve is :
> > > > > > >
> > > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > > certain "place holders" in a string.
> > > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > > permanent table.
> > > > > > >
> > > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > > >
> > > > > > > the temp table will be
> > > > > > > name | value
> > > > > > > --
> > > > > > > <name> | jack
> > > > > > > <age> | 23
> > > > > > > <city> | NY
> > > > > > >
> > > > > > > appreciate your help, thanks!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > John Bell wrote:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > > >
> > > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "Rishi" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > > concurrency problem.
> > > > > > > > >
> > > > > > > > > declare @.string
> > > > > > > > >
> > > > > > > > > CREATE TABLE #temp
> > > > > > > > > (
> > > > > > > > > name VARCHAR(100),
> > > > > > > > > value VARCHAR(100)
> > > > > > > > > )
> > > > > > > > > --INSERT some value
> > > > > > > > >
> > > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > > >
> > > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > > >
> > > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > > entries in #temp
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > > concurrent users execute it?
> > > > > > > > >
> > > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessary
> to pass the table name. For more on temporary tables read Books Online or at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure that
> tempdb is on it's own discs. Without knowing more about what you are trying
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
> > Hi,
> > I want to know if the below mentioned pseudo code can pose a
> > concurrency problem.
> >
> > declare @.string
> >
> > CREATE TABLE #temp
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> > --INSERT some value
> >
> > --Call another stored procedure that takes the table name and a string
> >
> > EXEC usp_replace '#temp',@.string OUTPUT
> >
> > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > entries in #temp
> >
> >
> >
> > Now the question is that since im passing the table name and using it
> > to query in the usp_replace could this mess up the values if multiple
> > concurrent users execute it?
> >
> > Would really appreciate any thoughts on this. Thanks.
> >
> >|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:
> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
> > Hi
> >
> > If you have created the temporary table at the outer level it will be
> > available to the inner level procedure, therefore it should not be necessary
> > to pass the table name. For more on temporary tables read Books Online or at
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> >
> > If many processes are creating/dropping temporary tables you can get
> > contention on tempdb, this can be limited to some degree by making sure that
> > tempdb is on it's own discs. Without knowing more about what you are trying
> > to achieve it is not possible to suggest an alternative approach.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > I want to know if the below mentioned pseudo code can pose a
> > > concurrency problem.
> > >
> > > declare @.string
> > >
> > > CREATE TABLE #temp
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > > --INSERT some value
> > >
> > > --Call another stored procedure that takes the table name and a string
> > >
> > > EXEC usp_replace '#temp',@.string OUTPUT
> > >
> > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > entries in #temp
> > >
> > >
> > >
> > > Now the question is that since im passing the table name and using it
> > > to query in the usp_replace could this mess up the values if multiple
> > > concurrent users execute it?
> > >
> > > Would really appreciate any thoughts on this. Thanks.
> > >
> > >
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
> > Thanks John!
> > to be more precise on what im tryin to achieve is :
> >
> > To create a generic stored procedure that replace the occurence of
> > certain "place holders" in a string.
> > The placeholders' values are stored in a table as a name value pair.
> > This table can be either generates on the fly as a temp table or be a
> > permanent table.
> >
> > e.g. the string is '<name> who is <age> years old lives in <city>'
> >
> > the temp table will be
> > name | value
> > --
> > <name> | jack
> > <age> | 23
> > <city> | NY
> >
> > appreciate your help, thanks!
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If you have created the temporary table at the outer level it will be
> > > available to the inner level procedure, therefore it should not be necessary
> > > to pass the table name. For more on temporary tables read Books Online or at
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > >
> > > If many processes are creating/dropping temporary tables you can get
> > > contention on tempdb, this can be limited to some degree by making sure that
> > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > to achieve it is not possible to suggest an alternative approach.
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > I want to know if the below mentioned pseudo code can pose a
> > > > concurrency problem.
> > > >
> > > > declare @.string
> > > >
> > > > CREATE TABLE #temp
> > > > (
> > > > name VARCHAR(100),
> > > > value VARCHAR(100)
> > > > )
> > > > --INSERT some value
> > > >
> > > > --Call another stored procedure that takes the table name and a string
> > > >
> > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > >
> > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > entries in #temp
> > > >
> > > >
> > > >
> > > > Now the question is that since im passing the table name and using it
> > > > to query in the usp_replace could this mess up the values if multiple
> > > > concurrent users execute it?
> > > >
> > > > Would really appreciate any thoughts on this. Thanks.
> > > >
> > > >
> >
> >|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:
> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > If these are just values then you should be able to join to this table and
> > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > e.g. column names then
> > see
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > but still pass variables for the values. You may want to look at
> > http://www.sommarskog.se/dynamic_sql.html and
> > http://www.sommarskog.se/dyn-search.html
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Thanks John!
> > > to be more precise on what im tryin to achieve is :
> > >
> > > To create a generic stored procedure that replace the occurence of
> > > certain "place holders" in a string.
> > > The placeholders' values are stored in a table as a name value pair.
> > > This table can be either generates on the fly as a temp table or be a
> > > permanent table.
> > >
> > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > >
> > > the temp table will be
> > > name | value
> > > --
> > > <name> | jack
> > > <age> | 23
> > > <city> | NY
> > >
> > > appreciate your help, thanks!
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If you have created the temporary table at the outer level it will be
> > > > available to the inner level procedure, therefore it should not be necessary
> > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > >
> > > > If many processes are creating/dropping temporary tables you can get
> > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > to achieve it is not possible to suggest an alternative approach.
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > concurrency problem.
> > > > >
> > > > > declare @.string
> > > > >
> > > > > CREATE TABLE #temp
> > > > > (
> > > > > name VARCHAR(100),
> > > > > value VARCHAR(100)
> > > > > )
> > > > > --INSERT some value
> > > > >
> > > > > --Call another stored procedure that takes the table name and a string
> > > > >
> > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > >
> > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > entries in #temp
> > > > >
> > > > >
> > > > >
> > > > > Now the question is that since im passing the table name and using it
> > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > concurrent users execute it?
> > > > >
> > > > > Would really appreciate any thoughts on this. Thanks.
> > > > >
> > > > >
> > >
> > >
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
------
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
John Bell wrote:
> Hi
> Can you give an example SQL Statement and the values in the table that will
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
> > Hi,
> > the dynamic sql thing looks great, but i dont know how it will
> > rescue me as a n aternative in this case. could you please throw some
> > more light on this.
> > probably my last post wasnt very clear, so the deal is:
> >
> > i have a table which has name value pairs.
> > i have a string which has names as place holders for the values
> > in the string i need to replace those names with the values as found in
> > the table.
> > i want a general reusable stored proc to acheive this.
> > the way i am doing it as desribed in the first post works, but i want
> > to know if that will pose any concurrency problems.
> > since i want the sp to general and reusable i dont want to hard code
> > and use the table name in the usp_replace sp.
> >
> > Please let me know your thoughts.
> > Thanks,
> > Rishi.
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If these are just values then you should be able to join to this table and
> > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > e.g. column names then
> > > see
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > but still pass variables for the values. You may want to look at
> > > http://www.sommarskog.se/dynamic_sql.html and
> > > http://www.sommarskog.se/dyn-search.html
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Thanks John!
> > > > to be more precise on what im tryin to achieve is :
> > > >
> > > > To create a generic stored procedure that replace the occurence of
> > > > certain "place holders" in a string.
> > > > The placeholders' values are stored in a table as a name value pair.
> > > > This table can be either generates on the fly as a temp table or be a
> > > > permanent table.
> > > >
> > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > >
> > > > the temp table will be
> > > > name | value
> > > > --
> > > > <name> | jack
> > > > <age> | 23
> > > > <city> | NY
> > > >
> > > > appreciate your help, thanks!
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If you have created the temporary table at the outer level it will be
> > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > >
> > > > > If many processes are creating/dropping temporary tables you can get
> > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > to achieve it is not possible to suggest an alternative approach.
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Hi,
> > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > concurrency problem.
> > > > > >
> > > > > > declare @.string
> > > > > >
> > > > > > CREATE TABLE #temp
> > > > > > (
> > > > > > name VARCHAR(100),
> > > > > > value VARCHAR(100)
> > > > > > )
> > > > > > --INSERT some value
> > > > > >
> > > > > > --Call another stored procedure that takes the table name and a string
> > > > > >
> > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > >
> > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > entries in #temp
> > > > > >
> > > > > >
> > > > > >
> > > > > > Now the question is that since im passing the table name and using it
> > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > concurrent users execute it?
> > > > > >
> > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:
> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ------
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
>
>
> John Bell wrote:
> > Hi
> >
> > Can you give an example SQL Statement and the values in the table that will
> > be used and the code to usp_replace?
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > the dynamic sql thing looks great, but i dont know how it will
> > > rescue me as a n aternative in this case. could you please throw some
> > > more light on this.
> > > probably my last post wasnt very clear, so the deal is:
> > >
> > > i have a table which has name value pairs.
> > > i have a string which has names as place holders for the values
> > > in the string i need to replace those names with the values as found in
> > > the table.
> > > i want a general reusable stored proc to acheive this.
> > > the way i am doing it as desribed in the first post works, but i want
> > > to know if that will pose any concurrency problems.
> > > since i want the sp to general and reusable i dont want to hard code
> > > and use the table name in the usp_replace sp.
> > >
> > > Please let me know your thoughts.
> > > Thanks,
> > > Rishi.
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If these are just values then you should be able to join to this table and
> > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > e.g. column names then
> > > > see
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > but still pass variables for the values. You may want to look at
> > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > http://www.sommarskog.se/dyn-search.html
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Thanks John!
> > > > > to be more precise on what im tryin to achieve is :
> > > > >
> > > > > To create a generic stored procedure that replace the occurence of
> > > > > certain "place holders" in a string.
> > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > This table can be either generates on the fly as a temp table or be a
> > > > > permanent table.
> > > > >
> > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > >
> > > > > the temp table will be
> > > > > name | value
> > > > > --
> > > > > <name> | jack
> > > > > <age> | 23
> > > > > <city> | NY
> > > > >
> > > > > appreciate your help, thanks!
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If you have created the temporary table at the outer level it will be
> > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > >
> > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > concurrency problem.
> > > > > > >
> > > > > > > declare @.string
> > > > > > >
> > > > > > > CREATE TABLE #temp
> > > > > > > (
> > > > > > > name VARCHAR(100),
> > > > > > > value VARCHAR(100)
> > > > > > > )
> > > > > > > --INSERT some value
> > > > > > >
> > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > >
> > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > >
> > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > entries in #temp
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > concurrent users execute it?
> > > > > > >
> > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you have
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
> > Sure John! here it is:
> >
> >
> > --- in main stored proc
> > ----
> >
> > --Some code goes here
> >
> > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > <property1>'
> >
> > CREATE TABLE #prop
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> >
> > -- Get Standard Attributes
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> >
> > -- Get user defined properties
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> > --
> > --
> > --
> >
> > EXEC usp_replace '#prop',@.string output
> >
> >
> >
> >
> > ------
> >
> > ---
> > usp_replace----
> >
> > ALTER PROCEDURE [dbo].[usp_replace]
> >
> > @.tablename VARCHAR(100) ,
> > @.string VARCHAR(1000) OUTPUT
> > AS
> > BEGIN
> > --IF(nullif(@.string,'') is null) RETURN
> >
> > DECLARE @.tableqry VARCHAR(100)
> > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> >
> > SET @.tableqry = 'SELECT * FROM '+@.tablename
> >
> > INSERT INTO @.temp
> > EXEC (@.tableqry)
> >
> > DECLARE c CURSOR FOR
> > SELECT * FROM @.temp
> >
> > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > OPEN c
> > FETCH next FROM c INTO @.name,@.value
> > WHILE @.@.fetch_status =0 BEGIN
> > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> >
> > FETCH next FROM c INTO @.name,@.value
> >
> > END
> > CLOSE c
> > DEALLOCATE c
> >
> >
> > END
> >
> > ---
> > usp_replace----
> >
> >
> > the table #prop looks like this after values are inserted in it in the
> > main sp.
> >
> > name | value
> > ---
> > <attribute1> | attributvalue
> > <attribute2 > | attribute2value
> > ----
> >
> >
> > finallly the string looks like this after returned from sp_replace
> >
> > ' attributvalue some text here attribute2value some text here
> > <property1>'
> > ----
> >
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Can you give an example SQL Statement and the values in the table that will
> > > be used and the code to usp_replace?
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > the dynamic sql thing looks great, but i dont know how it will
> > > > rescue me as a n aternative in this case. could you please throw some
> > > > more light on this.
> > > > probably my last post wasnt very clear, so the deal is:
> > > >
> > > > i have a table which has name value pairs.
> > > > i have a string which has names as place holders for the values
> > > > in the string i need to replace those names with the values as found in
> > > > the table.
> > > > i want a general reusable stored proc to acheive this.
> > > > the way i am doing it as desribed in the first post works, but i want
> > > > to know if that will pose any concurrency problems.
> > > > since i want the sp to general and reusable i dont want to hard code
> > > > and use the table name in the usp_replace sp.
> > > >
> > > > Please let me know your thoughts.
> > > > Thanks,
> > > > Rishi.
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If these are just values then you should be able to join to this table and
> > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > e.g. column names then
> > > > > see
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > but still pass variables for the values. You may want to look at
> > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > http://www.sommarskog.se/dyn-search.html
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Thanks John!
> > > > > > to be more precise on what im tryin to achieve is :
> > > > > >
> > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > certain "place holders" in a string.
> > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > permanent table.
> > > > > >
> > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > >
> > > > > > the temp table will be
> > > > > > name | value
> > > > > > --
> > > > > > <name> | jack
> > > > > > <age> | 23
> > > > > > <city> | NY
> > > > > >
> > > > > > appreciate your help, thanks!
> > > > > >
> > > > > >
> > > > > >
> > > > > > John Bell wrote:
> > > > > > > Hi
> > > > > > >
> > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > >
> > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Rishi" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > concurrency problem.
> > > > > > > >
> > > > > > > > declare @.string
> > > > > > > >
> > > > > > > > CREATE TABLE #temp
> > > > > > > > (
> > > > > > > > name VARCHAR(100),
> > > > > > > > value VARCHAR(100)
> > > > > > > > )
> > > > > > > > --INSERT some value
> > > > > > > >
> > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > >
> > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > >
> > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > entries in #temp
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > concurrent users execute it?
> > > > > > > >
> > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if you
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:
> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > As your strings with the placeholders are not SQL Statements then don't
> > think I can suggest a different alternative.
> >
> > I am not sure why you need to dynamic SQL for returning values (or to pass
> > the t able name) from your temporary table, unless the substitutions will be
> > embeded it should not be necessary! You may want to try ordering the
> > processing by the length of the value to try and avoid replacing already
> > substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> > table variable? For you string size you will may have truncation if you have
> > more than 9 substitutions.
> >
> > If this is just going to be sent to the client you may just want to do the
> > replacement on the client.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Sure John! here it is:
> > >
> > >
> > > --- in main stored proc
> > > ----
> > >
> > > --Some code goes here
> > >
> > > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > > <property1>'
> > >
> > > CREATE TABLE #prop
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > >
> > > -- Get Standard Attributes
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > >
> > > -- Get user defined properties
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > > --
> > > --
> > > --
> > >
> > > EXEC usp_replace '#prop',@.string output
> > >
> > >
> > >
> > >
> > > ------
> > >
> > > ---
> > > usp_replace----
> > >
> > > ALTER PROCEDURE [dbo].[usp_replace]
> > >
> > > @.tablename VARCHAR(100) ,
> > > @.string VARCHAR(1000) OUTPUT
> > > AS
> > > BEGIN
> > > --IF(nullif(@.string,'') is null) RETURN
> > >
> > > DECLARE @.tableqry VARCHAR(100)
> > > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> > >
> > > SET @.tableqry = 'SELECT * FROM '+@.tablename
> > >
> > > INSERT INTO @.temp
> > > EXEC (@.tableqry)
> > >
> > > DECLARE c CURSOR FOR
> > > SELECT * FROM @.temp
> > >
> > > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > > OPEN c
> > > FETCH next FROM c INTO @.name,@.value
> > > WHILE @.@.fetch_status =0 BEGIN
> > > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> > >
> > > FETCH next FROM c INTO @.name,@.value
> > >
> > > END
> > > CLOSE c
> > > DEALLOCATE c
> > >
> > >
> > > END
> > >
> > > ---
> > > usp_replace----
> > >
> > >
> > > the table #prop looks like this after values are inserted in it in the
> > > main sp.
> > >
> > > name | value
> > > ---
> > > <attribute1> | attributvalue
> > > <attribute2 > | attribute2value
> > > ----
> > >
> > >
> > > finallly the string looks like this after returned from sp_replace
> > >
> > > ' attributvalue some text here attribute2value some text here
> > > <property1>'
> > > ----
> > >
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Can you give an example SQL Statement and the values in the table that will
> > > > be used and the code to usp_replace?
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > the dynamic sql thing looks great, but i dont know how it will
> > > > > rescue me as a n aternative in this case. could you please throw some
> > > > > more light on this.
> > > > > probably my last post wasnt very clear, so the deal is:
> > > > >
> > > > > i have a table which has name value pairs.
> > > > > i have a string which has names as place holders for the values
> > > > > in the string i need to replace those names with the values as found in
> > > > > the table.
> > > > > i want a general reusable stored proc to acheive this.
> > > > > the way i am doing it as desribed in the first post works, but i want
> > > > > to know if that will pose any concurrency problems.
> > > > > since i want the sp to general and reusable i dont want to hard code
> > > > > and use the table name in the usp_replace sp.
> > > > >
> > > > > Please let me know your thoughts.
> > > > > Thanks,
> > > > > Rishi.
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If these are just values then you should be able to join to this table and
> > > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > > e.g. column names then
> > > > > > see
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > > but still pass variables for the values. You may want to look at
> > > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > > http://www.sommarskog.se/dyn-search.html
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Thanks John!
> > > > > > > to be more precise on what im tryin to achieve is :
> > > > > > >
> > > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > > certain "place holders" in a string.
> > > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > > permanent table.
> > > > > > >
> > > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > > >
> > > > > > > the temp table will be
> > > > > > > name | value
> > > > > > > --
> > > > > > > <name> | jack
> > > > > > > <age> | 23
> > > > > > > <city> | NY
> > > > > > >
> > > > > > > appreciate your help, thanks!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > John Bell wrote:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > > >
> > > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "Rishi" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > > concurrency problem.
> > > > > > > > >
> > > > > > > > > declare @.string
> > > > > > > > >
> > > > > > > > > CREATE TABLE #temp
> > > > > > > > > (
> > > > > > > > > name VARCHAR(100),
> > > > > > > > > value VARCHAR(100)
> > > > > > > > > )
> > > > > > > > > --INSERT some value
> > > > > > > > >
> > > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > > >
> > > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > > >
> > > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > > entries in #temp
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > > concurrent users execute it?
> > > > > > > > >
> > > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
Saturday, February 25, 2012
Possible Bug Using TOP and Paging via a Temp Table
This is not a "bug" per se, as I know that the order of rows in SQL
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>
Monday, February 20, 2012
Position opening : Web Database Programmer (Part-time temp)
I am a small business entrepreneur, and interested in
developing my own web site.
I am currently looking for a Database Programmer, who
has the skill set of MySQL , PHP running on a Linux AS
Platform.
Time is essence for this project. The front-end has
been built properly even though has not been launched
yet, we need the back- end to be done with in 3 weeks.
If you are interested in helping us to build the
back-end
Pls email to me at hhconsultinginc@.yahoo.com with your working experience or Resume.
Let me know if you have any question.
LarryWhere in Jersey?
And to build the front end then build then backened is ah...backwards...
What is the app meant to do?
Did you build an ER logical model?
Any Business flow models?
Data Models?
who is and how big is the User community?
developing my own web site.
I am currently looking for a Database Programmer, who
has the skill set of MySQL , PHP running on a Linux AS
Platform.
Time is essence for this project. The front-end has
been built properly even though has not been launched
yet, we need the back- end to be done with in 3 weeks.
If you are interested in helping us to build the
back-end
Pls email to me at hhconsultinginc@.yahoo.com with your working experience or Resume.
Let me know if you have any question.
LarryWhere in Jersey?
And to build the front end then build then backened is ah...backwards...
What is the app meant to do?
Did you build an ER logical model?
Any Business flow models?
Data Models?
who is and how big is the User community?
Subscribe to:
Posts (Atom)