Prev: Using Float Type Variables for Primary on SQL 2005
Next: data type equivalent to double
From: Passiday on 19 Jul 2010 13:25
Hello,

I am looking for a way how to select field value, if the table and
field name both are available as string parameters.

I am aware that in SP it could be done, by using exec function. I am
also aware of the fact, that this is kind of bad practice, ie it
includes SQL injection threat, is not effective use of server engine,
etc. Nevertheless, I hope that I will be shown the solution rathter
than taught not to try.

The reason I need such function, is the nature of record IDs in the
database - they are not automatically populated, and they are random
(rather than consequitive). The reason it was done so was to be able
to first generate valid ID and then use it for parent and child query
generation in one shot. The chance that parallel session would
generate the same ID in the few millisecs while the present query is
executed, is infinitesimally small, so this approach can be considered
to be reasonably safe.

Generating valid ID is simple at the software level: generate random
int, test if it's not already used, repeat until no match is found.
But it brings difficulties, if I want to populate a table by single
query: generating the ID should be done via help of UDF. Of course, a
seperate UDF can be made for every table, but that feels kind of
repetitive. I hope to have an UDF like GetID(tableName, fieldName)
that generates and returns valid ID.

In the body of this function I basically need to be able to execute a
very simple query:
SET @IsValid = SELECT @FieldName FROM @TableName WHERE @FieldName =
@TestID
So, I am hoping that maybe it is possible to select this value through
"backdoor" - using the system tables where the actual data are stored
in low-level meta-data format.

Passiday
From: John Bell on 19 Jul 2010 15:33
On Mon, 19 Jul 2010 10:25:43 -0700 (PDT), Passiday
<passiday(a)gmail.com> wrote:

>Hello,
>
>I am looking for a way how to select field value, if the table and
>field name both are available as string parameters.
>
>I am aware that in SP it could be done, by using exec function. I am
>also aware of the fact, that this is kind of bad practice, ie it
>includes SQL injection threat, is not effective use of server engine,
>etc. Nevertheless, I hope that I will be shown the solution rathter
>than taught not to try.
>
>The reason I need such function, is the nature of record IDs in the
>database - they are not automatically populated, and they are random
>(rather than consequitive). The reason it was done so was to be able
>to first generate valid ID and then use it for parent and child query
>generation in one shot. The chance that parallel session would
>generate the same ID in the few millisecs while the present query is
>executed, is infinitesimally small, so this approach can be considered
>to be reasonably safe.
>
>Generating valid ID is simple at the software level: generate random
>int, test if it's not already used, repeat until no match is found.
>But it brings difficulties, if I want to populate a table by single
>query: generating the ID should be done via help of UDF. Of course, a
>seperate UDF can be made for every table, but that feels kind of
>repetitive. I hope to have an UDF like GetID(tableName, fieldName)
>that generates and returns valid ID.
>
>In the body of this function I basically need to be able to execute a
>very simple query:
> SET @IsValid = SELECT @FieldName FROM @TableName WHERE @FieldName =
>@TestID
>So, I am hoping that maybe it is possible to select this value through
>"backdoor" - using the system tables where the actual data are stored
>in low-level meta-data format.
>
>Passiday

I don;t think there is a way to do what you want without using Dynamic
SQL, but I am not sure why you aren't using GUIDs/uniqueidentifiers?
Even though they are horrible, they are probably better than what you
are currently doing!

John
From: Passiday on 19 Jul 2010 16:40
Hi,

> I am not sure why you aren't using GUIDs/uniqueidentifiers?
> Even though they are horrible, they are probably better than what you
> are currently doing!

This project has long legacy, it's been ported through different
database platforms. In the environment where it's core structures and
code was developed, there was no such thing as reliable GUIDs.
Besides, I can't agree that the current ID generation would be so
"horrible" - generating new ID at code level is quite simple.

Pavils
From: Eric Isaacs on 19 Jul 2010 16:54
Use an INT IDENTITY or a UNIQUEIDENTIFIER. Either will a lot faster
and more reliable than what you're proposing, especially for bulk
inserts.

-Eric Isaacs
From: Erland Sommarskog on 19 Jul 2010 17:49
Passiday (passiday(a)gmail.com) writes:
> Generating valid ID is simple at the software level: generate random
> int, test if it's not already used, repeat until no match is found.
> But it brings difficulties, if I want to populate a table by single
> query: generating the ID should be done via help of UDF. Of course, a
> seperate UDF can be made for every table, but that feels kind of
> repetitive. I hope to have an UDF like GetID(tableName, fieldName)
> that generates and returns valid ID.

Just forget it. But rather than having on ID pool per table, you could
have a common pool that you take all IDs from. Then you only need one UDF.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Using Float Type Variables for Primary on SQL 2005
Next: data type equivalent to double