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:
>