Dynamic SQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

dvadithala
Starting Member

1 Post

Posted - 2009-11-19 : 16:37:53

Hi there,

I am new SQL Server and I was given a task to write a simple Stored Procedure. Requirement is to lookup the column name dynamically and update the rows based on the condition. I had started to create my first stored procedure (never written one!!!). So I wanted to if this correct approach or is there a simpler and effective way to wrie the Proc. Any suggestions will be great help. Many thanks to all of you.

____________________________________________________________________

Here is the procedure (which I am trying):
USE [testdb]
GO
/****** Object: StoredProcedure [dbo].[usp_Recode_Same_Variables] Script Date: 11/19/2009 19:05:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_Recode_Same_Variables] -- creating/ altering the procedure
@Project_Name varchar(120),
@Survey_Name varchar(120) = 'Survey',
@Outcome_Column varchar(50) = 'completed',
@Old_Column_Name varchar(10), -- declaring input parameters
@New_Column_Name varchar(10),
@New_Values int,
@Old_Values int,
@Recode_Type varchar(50)

As
Begin Tran Recoding_New_Column -- Beginning the transaction
declare @Mycommand nvarchar(50) -- declaring the variables
declare @Newquestion nvarchar(50)
declare @Oldquestion nvarchar(50)
declare @mynewvalues varchar(10)
declare @myoldvalues varchar(10)
declare @ProjectName varchar(120)
declare @mysql nvarchar(500)
declare @survey varchar(120)
declare @outcome varchar(50)
declare @recodetype varchar(50)

Set @Mycommand = 'Update Test_data set ' -- setting/Initializing the variables
set @Newquestion = @New_Column_Name
set @Oldquestion = @Old_Column_Name
Set @mynewvalues = @New_Values
Set @myoldvalues = @Old_Values
Set @ProjectName = @Project_Name
set @survey = @Survey_Name
set @outcome = @Outcome_Column
set @recodetype = @recode_type

IF @Recode_Type = 'Single'
Begin
IF @myoldvalues is null -- Checks the condition if the old values contains null
set @mysql = @mycommand + @Newquestion + '=' + @mynewvalues + 'where ' + @Oldquestion + ' is null' + ' and ' + @Survey + ' = '''+ @ProjectName + '''' + ' and ' + 'Outcome' + ' = '''+ @Outcome + '''' -- Executes the statement if the old values are null
Else
set @mysql = @mycommand + @Newquestion + '=' + @mynewvalues + 'where ' + @Oldquestion + '=' + @Myoldvalues + ' and ' + @Survey + ' = '''+ @ProjectName + '''' + ' and ' + 'Outcome' + ' = '''+ @Outcome + '''' -- Executes the statement if the old values are not null
End

Exec (@Mysql) -- Executes the batch of statements
Commit Tran Recoding_New_Column -- committing the transaction and updating the valueses

Cheers - DV

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 18:37:10

Who gave you those requirements?

a .NET Developer?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources