Passing field name as variable to a query in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing field name as variable to a query in SP

Author

Topic

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 09:01:34

Hi I am trying to pass a field name to a stored procedure to get the result. I don't want to create a sql string and execute the sql string. is there any bway we can pass field name as a variable to a stored procedure. here is the example I am workin on

declare @myfieldname varchar(100)

set @myfieldname ='City'

select @myfieldname from tblAddress

the above code doesn't work
Is there any way we can pass a field name as a variable to a sp or function. Please let me know. Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-06 : 09:23:18

quote:
I don't want to create a sql string and execute the sql string

Then you have no option, as dynamic SQL is the only way you would be able to do what you are asking for. However, I think you are right in wanting to avoid it.

-------
Moo. :)

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 09:28:07

Thank you Moo.

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 09:45:52

I have a question is there any way we can pass the result of an executed dynamic sql statement to a variable again. Please let me know. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-06 : 13:28:51

Siva, why would you want to do this? Perhaps if you gave us an example of what you are trying to do, we would be able to come up with a solutin for you.

Tara

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 13:39:43

Hi Tara,
I am working on a project named validations. In this I created a table named tblrules. In the tblrules we store all our business rules, check and apply these rules dynamically when we enter new data in to another table.

I am using dynamic SQl statements in a cursor. At one stage we need to create dynamically a query that needs to pass filed names to query. Any help is highly appreciated. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-06 : 13:44:56

Why do you need to pass field names to a query though? Can't you write out all of the different queries in your stored procedure? So what if your stored procedure is long, at least it is good code.

BTW, not only should you not use dynamic sql, but you should also not use cursors.

Is this for Access? If yes, then please post Access questions here:

[url]http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3[/url]

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 13:47:50

you should enforce as many validations as possible via foreign key constraints and CHECK() constraints.

- Jeff

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 13:56:49

In our tblrules, users will apply existing field rules to other new fields. Some times they exclude existing rules. They retire some rules after some time. So I need a dynamic query to pass field names. Writing a query for each field is not effecient. And we don't know when/ which field they are going to apply what rule. This is new kind of a databse. It is a SQL Server 2000 DB. Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-06 : 14:00:50

quote:
Originally posted by Siva Cherukupally

Writing a query for each field is not effecient.

It might not be efficient for the programmer, but it is much more efficient for the database server. Dynamic sql will negatively impact your query's performance. You should reconsider going down this route.

Tara

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 14:09:18

This is a small database on a big server. So the impact is minimal.
My question is, is there any way we can pass a field name via variable to a sql query? Not to a SQL string. Thanks for your help.

Example:
declare @myfieldname varchar(100)

set @myfieldname ='City'

select @myfieldname from tblAddress

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-06 : 15:01:51

Yes you can do that and it is called dynamic sql.

Here is an example:



DECLARE @SQL VARCHAR(8000
DECLARE @myfieldname SYSNAME

SET @myfieldname = 'City'

SELECT @SQL = 'SELECT ' + @myfieldname + ' FROM tblAddress'

EXEC @SQL

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-06 : 15:09:19

quote:
Originally posted by tduggan

Yes you can do that and it is called dynamic sql.

Here is an example:

DECLARE @SQL VARCHAR(8000
DECLARE @myfieldname SYSNAME

SET @myfieldname = 'City'

SELECT @SQL = 'SELECT ' + @myfieldname + ' FROM tblAddress'

EXEC @SQL

Tara

Ya know....we only have so many keys to the kingdom...

Brett

8-)

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 15:11:40

Thanks Tara,
I know this way. I don't want to create a sql string and execute the sql string. If we create and execute it just simply executes it. How can we tap/pass the result of the executed statement to another variable or query? That is where I stuck. Any help is highly appreciated. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-06 : 15:13:20

Put the results into a temp table:

SELECT *
INTO #Temp
FROM Table1
...

You might need to use a global temp table (put ## instead of # before the table name) though since the query will run in a different session.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 15:16:26

Siva --

give us an example of one of your business rules, and what you would like to do.

- Jeff

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 15:20:06

Thanks Tara,
Right now I am doing that way. Is it the only solution? Or can we pass it to a variable by some other way. Please let me know. Thanks

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 15:30:57

For example the user in US can not assign a social to more than one person. Where as the other user in India can assign the social to multiple users like this '999999999' since there are no social security number concept exist in India.
This is one example I am giving.
Some rules are applicable to only some regions.
There is also a start date and an expiration date for the rule. Before and after the expiration date that rule should be ignored.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 15:59:25

Are these rules to be implemented as triggers, or in data entry forms in the user interface, or as reports that are run periodically to validate existing data?

- Jeff

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 16:17:35

The archetecture is like this. The front end writes to a validation table before it inserts into the main table and calls the stored procdure to check all validations. The stored procedure checks and populates to an error table if any rules are violated. If there are no errors the sp inserts the data silently in to the main table.
Note: We can not change the databse/Table structure it is a production one and several front ends talk to it constantly.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-06 : 16:19:24

Sounds like a table that I would interogate in a sproc and use control of flow logic to decide what to do..

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources