Passing variable in a Stored Procedure

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 2005 Forums

Transact-SQL (2005)

Passing variable in a Stored Procedure

Author

Topic

eepman25
Starting Member

5 Posts

Posted - 2007-07-16 : 11:53:59

Hello! I have an ASP.NET application that will be passing some variables to a SP. What I would like to do is take those variables (tableName, tableColumn, recordID, txtTO) and then check it against an existing table to see if text has changed. Here is a crude example:

SELECT tableColumn FROM tableName WHERE PRIMARY_KEY = recordID

I then want to take what that returns and assign it to another variable called txtFrom.

Then I wish to have an if statement to check if txtFrom and txtTo are the same. If they are not then I wish to insert the information into a different table for auditing purposes.

I am having problems getting the select statement to return a value that I can then pass onto the IF statement...

Any help would be greatly appreciated!

Eric

eepman25
Starting Member

5 Posts

Posted - 2007-07-16 : 11:59:55

Below is my crude attemtp at doing this... as I'm sure you are aware it does not work... Please help...

ALTER PROCEDURE [dbo].[Audit]
-- Add the parameters for the stored procedure here
@tableName nvarchar(50),
@columnName nvarchar(50),
@recordID int,
@txtTo nvarchar(50),
@txtFrom nvarchar(50),
@sysUserID int,
@sysDateTime datetime
AS

DECLARE @SQLStatement nvarchar(255)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT @SQLStatement = 'SELECT @txtFrom = ' + @columnName + ' FROM ' + @tableName + ' WHERE customerID = ' + recordID

EXEC(@SQLStatement)

IF NOT (@txtFrom = @txtTo)
BEGIN
INSERT dAudit (tableName, columnName, recordID, txtFrom, txtTo, sysUserID, sysDateTime)
VALUES (@tableName, @columnName, @recordID, @txtFrom, @txtTo, @sysUserID, @sysDateTime)
END

END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 12:05:02

The variable @txtfrom is in a different scope when its inside the dynamic sql than when its outside. One way is to create a temp table with a single column and insert the value into it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 12:08:59

Something like this:


ALTER PROCEDURE [dbo].[Audit] 
	-- Add the parameters for the stored procedure here
	@tableName nvarchar(50), 
	@columnName nvarchar(50),
	@recordID int,
	@txtTo nvarchar(50),
	@txtFrom nvarchar(50),
	@sysUserID int,
	@sysDateTime datetime
AS

DECLARE @SQLStatement nvarchar(255)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Create Table #tmp1 ( ColumnName varchar(50))

SELECT @SQLStatement = 'Insert into #tmp1 (ColumnName) SELECT ' + @columnName + ' FROM ' + @tableName + ' WHERE customerID = ' + convert(varchar,recordID)

EXEC(@SQLStatement)

Select @txtFrom = ColumnName from #tmp1 

IF NOT (@txtFrom = @txtTo)
	BEGIN
		INSERT dAudit (tableName, columnName, recordID, txtFrom, txtTo, sysUserID, sysDateTime)
		VALUES (@tableName, @columnName, @recordID, @txtFrom, @txtTo, @sysUserID, @sysDateTime)
	END

Drop Table #tmp1

END

Also, notice that you have to use a convert function to convert the RecordID to varchar. You are concatenating a string value with integer value and SQL will not accept that.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eepman25
Starting Member

5 Posts

Posted - 2007-07-16 : 12:29:16

Thank you for your help... Your code did not seem to work for me... this is what I have done so far, which does work...

ALTER PROCEDURE [dbo].[Audit]
-- Add the parameters for the stored procedure here
@tableName nvarchar(50),
@columnName nvarchar(50),
@recordID int,
@txtTo nvarchar(50),
@sysUserID int,
@sysDateTime datetime
AS

DECLARE @txtFrom nvarchar(50)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

CREATE TABLE TEMP (txtFrom nvarchar(50))
INSERT TEMP
EXEC('SELECT ' + @columnName + ' FROM ' + @tableName + ' WHERE customerID = '+ @recordID)
SELECT @txtFrom = txtFrom FROM TEMP
DROP TABLE TEMP

IF NOT (@txtFrom = @txtTo)
BEGIN
INSERT dAudit (tableName, columnName, recordID, txtFrom, txtTo, sysUserID, sysDateTime)
VALUES (@tableName, @columnName, @recordID, @txtFrom, @txtTo, @sysUserID, @sysDateTime)
END

END

This next issue I would like to solve with this SP is the "Where" statement.

WHERE customerID = '+ @recordID

instead of using "customerID" I would like it to look at the primary key for that table. Is there a reserved word for that? I know if you want to return a primary key after you insert a record it is @@identity, but that does not seem to work in this instance.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 12:42:17

Creating a permanent table and dropping for each execution of stored proc can be a considerable resouce hog if the calls to the procs increases. Look into the temp table option I suggested.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eepman25
Starting Member

5 Posts

Posted - 2007-07-16 : 12:44:02

Ok... Any suggestions on finding a primary key for a record in a table?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 12:50:12

Check out sysconstraints in books on line.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources