dynamic stored procedure question
-
Hi
I have a dynamic stored procedure.
CREATE PROCEDURE sp_web_getPKIDFromNamedValue
(
@Table varchar(50),
@FieldName varchar(50),
@Value varchar(50)
)
AS
EXEC("select PKID from " + @Table )
GO
How do I get the PKID bit to be dynamic - so that it will return the value of the Primary Key on any table I pass through the @table parameter?
EG : @Table = mytable with pkid of mytablepkid or
@Table = myCustomerTable with pkid of myCustomerTablePKID
please help!!!
thanks
-
what exactly are you asking?
what do you mean by value?
you want the stored procedure to return to you the name of the column(s) that is the primary key for the table you pass in?
or do you need to find out a value from the primary key column for table passed in?
-
I would like to get out a value from the primary key column for the table psased in - like 57,58,59.
-
"Online AnonProfile" seems a bit paranoid as a "handle" to me
... whatever...This little ditty should help you in your quest for primary key info... I even took the time to put it in a format condusive to your dynamic SQL endeavors... please feel free to modify this demo to suit your needs.
--===== Declare the database to use
USE NorthWind
--===== If temp table to hold key info exists, drop it
IF OBJECT_ID('TempDB..#MyPKInfo') IS NOT NULL
DROP TABLE #MyPKInfo
--===== Create the temp table to hold key info in
CREATE TABLE #MyPKInfo
(
Table_Qualifier VARCHAR(128),
Table_Owner VARCHAR(128),
Table_Name VARCHAR(128),
Column_Name VARCHAR(128),
Key_Seq INT,
PK_Name VARCHAR(128)
)
--===== Declare local variables
DECLARE @PKColumns VARCHAR(1000) --This will hold what you need
DECLARE @Table VARCHAR(1000)
SET @Table = 'Order Details'
--===== Get the primary key info for your table and store it
-- so we can use it
INSERT INTO #MyPKInfo
EXEC sp_pkeys @Table
--===== Create the comma separated list of columns in the PK
-- and store in variable for use in Dynamic SQL
SELECT @PKColumns = ISNULL(@PKColumns+',','')+Column_Name
FROM #MyPKInfo
ORDER BY Key_Seq
--===== Print the columns just for demonstration purposes
PRINT @PKColumns
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you know the column name ahead of time, and I asusme you do because you seem to be passing the column name as a parameter, you could make a small change, like this:
CREATE PROCEDURE sp_web_getPKIDFromNamedValue
(
@Table varchar(50),
@FieldName varchar(50),
@Value varchar(50)
)
AS
EXEC('select ' + @fieldname + ' from ' + @Table )
GO
-
What value?
The value of the last record inserted, the first, are you including a where clause?
Similar to Mkeast suggestion
If i wanted the last record inserted.
CREATE PROCEDURE sp_web_getPKIDFromNamedValue
(
@Table varchar(50),
@FieldName varchar(50)
)
AS
EXEC('select max(' + @fieldname + ') from ' + @Table )
GO
-- If I wanted the pk value for a certain field
CREATE PROCEDURE sp_web_getPKIDFromNamedValue
(
@Table varchar(50),
@FieldName varchar(50),
@Value varchar(50)
)
AS
declare @sqlstring varchar(1000)
set @sqlstring = 'select Mypkfield from ' + @Table + ' where ' + @FieldName + ' = @Value ')
exec sp_executesql @Sqlstring, N'@Value varchar(50)',@Value
GO
This link should give you all you want.
http://www.sommarskog.se/dynamic_sql.html
If you don't know the name of the pk field, then you have to trudge through the system tables, or as Jeff suggested.
I have never ever needed a solution like this though.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply