PORTS USED: declare

Showing posts with label declare. Show all posts

Monday, March 26, 2012

PP: XML Variable DataLength returns as 5

Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttributes)
--
5
Question
======= How come I get a value of 5 even tough I passed nothing.Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan
> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttributes

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/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.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Wednesday, March 7, 2012

possible to concatenate text to Where clause?

declare @.s varchar(300)
set @.s = ' recID = 100'
Select * from tbl1 Where + @.s
This returns a syntax error. Is it possible to concatenate text like this
to the Where clause? What does the correct syntax look like?
Thanks,
Richbetter yet - is it possible to use If/Else or select case in a where clause?
"Rich" wrote:

> declare @.s varchar(300)
> set @.s = ' recID = 100'
> Select * from tbl1 Where + @.s
> This returns a syntax error. Is it possible to concatenate text like this
> to the Where clause? What does the correct syntax look like?
> Thanks,
> Rich|||I seriously doubt this will help, but here is a small sample.
Any/every time I've tried to put a case statement in the where clause, I
fall flat.
But here is a small sample anyways.
You might want to check my article at:
http://www.sqlservercentral.com/col...lem.as
p
also.
declare @.price float
select @.price = 0.00
select @.price = 2.9900
--select @.price = null
select top 5 * , title, price
from pubs.dbo.titles
where
case
when @.price = 0 then price --here i am saying.. i didn't actually supply a
price.. so match the price with the price (aka, it gets everything because
price will always match price )
when @.price is null then price
else @.price -- aka, since i actually supplied a @.price, then match
the @.price with the price
end
= price -- the db field
----
---
-- the CASE statement above will translated into 1 of the two items below
select top 5 * , title, price
from pubs.dbo.titles where price = price -- aka, will always match, so you
get everything
select top 5 * , title, price
from pubs.dbo.titles where @.price = price --aka, only if the db value for
price matches the variable value for @.price
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> better yet - is it possible to use If/Else or select case in a where
clause?
> "Rich" wrote:
>
this|||Thanks for your reply. This does help actually. What I was trying to do
was to use an SP as a recordsource for a form, but I changed my mind and
decided to go with an inline function. This works, and I can still use the
case statement inside the function.
May I ask, how do you make arguments optional for a function?
"sloan" wrote:

> I seriously doubt this will help, but here is a small sample.
> Any/every time I've tried to put a case statement in the where clause, I
> fall flat.
> But here is a small sample anyways.
> You might want to check my article at:
> http://www.sqlservercentral.com/col...lem.
asp
> also.
>
>
> declare @.price float
> select @.price = 0.00
> select @.price = 2.9900
> --select @.price = null
> select top 5 * , title, price
> from pubs.dbo.titles
>
> where
> case
> when @.price = 0 then price --here i am saying.. i didn't actually supply
a
> price.. so match the price with the price (aka, it gets everything because
> price will always match price )
> when @.price is null then price
> else @.price -- aka, since i actually supplied a @.price, then match
> the @.price with the price
> end
> = price -- the db field
> ----
--
> ---
> -- the CASE statement above will translated into 1 of the two items below
> select top 5 * , title, price
> from pubs.dbo.titles where price = price -- aka, will always match, so y
ou
> get everything
> select top 5 * , title, price
> from pubs.dbo.titles where @.price = price --aka, only if the db value fo
r
> price matches the variable value for @.price
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> clause?
> this
>
>|||Rich
Unless I am completely wrong, I feel its fairly straightforward. try this.
declare @.s varchar(300)
set @.s = ' recID = 100'
exec('Select * from tbl1 Where ' + @.s)|||You can do this, but it's a horrible idea if @.s is
user-input or based on user-supplied data or metadata.
If @.s is something like '1=1; drop table tbl1' and the
code is executed with sufficient privilege, boom! Bye,
bye table 1. Worse and less noticeable things can
happen, too.
Read
http://www.*sommarskog*.se/dynamic_sql.html
http://www.unixwiz.net/techtips/sql-injection.html
Steve Kass
Drew University
Omnibuzz wrote:

>Rich
> Unless I am completely wrong, I feel its fairly straightforward. try this
.
>declare @.s varchar(300)
>set @.s = ' recID = 100'
>exec('Select * from tbl1 Where ' + @.s)
>
>

Saturday, February 25, 2012

Possible Bug with SQl Server 2000

Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:
>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:
> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
> >Hi,
> >
> >Look at the simple set of SQL statements below: Execute the following on
> >Query Analyzer.
> >
> >declare @.a float
> >declare @.b float
> >set @.a=24353.02821769137
> >set @.b=36459.95
> >exec('select '+@.a+'/'+@.b)
> >
> >Ans=.6679392
> >
> >Now, if I change the value of @.b to 36459.96,
> >I get a value of 0. Integer division is performed just because the value of
> >@.b increased by .01
> >
> >This looks like bug with the internal workings of SQL. This is very
> >dangerous cause basic SQL assumption is that if a value is a float, the
> >division will also be a float division. Integer division on the other hand
> >gives erroneous results in this case.
> >
> >Please solve this problem as soon as possible.
> >
> >-Ling Yu.
> >
> >
>|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>
>>Ling Yu,
>> This is not a bug, but it is surprising, and in my opinion, the query
>>should actually cause a syntax error. I'm glad you raised the question,
>>and I bet other people have been affected by this behavior.
>>Here's why it occurs:
>>The query processor must evaluate the query string 'select '+@.a+'/'+@.b
>>as its first step. When strings are concatenated inside exec(), the +
>>operator is not a T-SQL + operator. The + is applied by the front-end
>>application and only after the entire query string is assembled does
>>that string go to the query processor to be interpreted as T-SQL.
>>Query Analyzer (or the system it uses to prepare queries for the engine)
>>follows different rules than T-SQL to interpret the + that puts strings
>>together within exec(). In T-SQL, the + operation would fail, because
>>it tries to convert 'select ' and '/' into floats and add. But here, @.a
>>and @.b are converted to strings. The way in which they are converted to
>>strings is not obvious, but you can see it if you execute
>>exec('select ' + @.a) and exec('select ' + @.b)
>>What's important to realize is that exec() receives only a string,
>>without any type information about the parameters. Unfortunately,
>>floats are converted to strings by the front-end using at most 6 decimal
>>digits of precision. Since @.a is converted in both cases to the string
>>'24353', how the + in the query (not the concatenation + in the exec) is
>>understood depends on the string @.b converts to. If @.b is converted to
>>the string '36459.9', the addition is performed using decimal
>>arithmetic. If @.b is converted to '36460', the query executed is
>>select 24353/36460, with 0 as the result. The types of @.a and @.b are
>>unknown to the query processor, which sees only the string
>>representations of these numbers.
>>While at first glance, you might think the select query here recognizes
>>that it is receiving floats, it is not. exec() can only receive a
>>string and must interpret the types of numbers from their string
>>representations. A safer approach is to use sp_executesql, with
>>parameters, or to add typecasts in your query string.
>>I hope that helps.
>>
>>PAI wrote:
>>
>>Hi,
>>Look at the simple set of SQL statements below: Execute the following on
>>Query Analyzer.
>>declare @.a float
>>declare @.b float
>>set @.a=24353.02821769137
>>set @.b=36459.95
>>exec('select '+@.a+'/'+@.b)
>>Ans=.6679392
>>Now, if I change the value of @.b to 36459.96,
>>I get a value of 0. Integer division is performed just because the value of
>>@.b increased by .01
>>This looks like bug with the internal workings of SQL. This is very
>>dangerous cause basic SQL assumption is that if a value is a float, the
>>division will also be a float division. Integer division on the other hand
>>gives erroneous results in this case.
>>Please solve this problem as soon as possible.
>>-Ling Yu.
>>
>>

Monday, March 26, 2012

PP: XML Variable DataLength returns as 5

Friday, March 23, 2012

Potential Concurrency Issue?

Potential Concurrency Issue?

Potential Concurrency Issue?

Wednesday, March 7, 2012

possible to concatenate text to Where clause?

Saturday, February 25, 2012

Possible Bug with SQl Server 2000

Possible Bug with SQl Server 2000

PORTS USED

Blog Archive

About Me