Can we pass table Field name in a Stored Procedure

[essa2000]

Dears ;
Hi,
Can we pass Table Field name in a stored procedure to make daynamic queries in a SP? Actaully , I want to use that sp in filtering data by passing different parameters in where cluase.
It can also be used where we have the same table structure.

Thanks in advance !

Regards ,
essa2000

 

[DougP]

I think you can refer to a specific column by it "Ordinal Position"

But I can't find an example in the books on-line.
look at object.ColumnID

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[essa2000]

Dear DougP ;
Hi ,
I'll check it.
I could not understand your question , please give me details , where are U using Bar-Codes ?

regards,
essa2000

 

[LordOfCode]

I have tested this sample in the Query Analyzer I hope it helps:

CREATE PROCEDURE SP_SAMPLE @cColumn VARCHAR(20) AS
 BEGIN
   DECLARE @cQuery VARCHAR(100)
   SET @cQuery	=	
   "SELECT "+@cColumn+" FROM CLIENT WHERE CLIENT_ID = '10'"
   EXEC(@cQuery)
 END

Edwin Dalorzo
edalorzo@hotmail.com

 

[essa2000]

Dears Edwin Dalorzo ;
Yes , your example is right and I have already got the solution of my problem. I used sp_executesql systems stored procedure for this purpose which executes complex strings.

I am writing example :

*******************************************************

CREATE PROCEDURE stp_Sample
@TableName varchar(40)
@FieldName varchar(40)
@FieldValue varchar(100)

AS

Declare @SQL nvharchar(500)

Set @SQL = 'Delete ' + @TableName + 'Where ' + @FieldName +
' = ' + cast(@FieldValue as varchar(10))

Exec sp_executesql @SQL

***********************************************************

after passing values let say
@Tablename Customer
@FieldName CustomerID
@FieldValue 5

@SQL = Delete Customer where CustomerID = 5

Casting is used to convert int into varchar , b/c of string query.

In this way we can make daynamic Queries for SELECT , INSERT , UPDATE and DELETE also. It is also very helpful where the table structure of few tables is same and we use only one stored procedure for each insert , select , delete and update.

I hope that it will be helpful for others.

Regards ,
essa2000