Column name pass as parameter in Proc

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

Column name pass as parameter in Proc

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2011-02-14 : 21:27:11

Hi,

I want to pass the column name in procedure as parameter, Can any one help how to do it..

I am doing like this... and it gives error

Create PROCEDURE [dbo].[spPara]
@Month varchar(15),
@Project varchar(15)
AS
BEGIN
Declare
@Freeze Numeric(18,4),
@Current Numeric(18,4)

SELECT @Freeze = sum('+ @Month + ') From TestProject Where Code=@Project
Group By ProjectCode

END

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-14 : 21:30:09

This is a very bad idea and indicates you've got a serious design issue. Please explain why it needs to be dynamic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

asm
Posting Yak Master

140 Posts

Posted - 2011-02-15 : 01:31:33

Yes, I known this is bad table structure... But on this i need to build a report.
So, please guide can we do it in dynamic or we have write if...else
for all 12 months

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-15 : 03:15:31

Here you go: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-15 : 05:15:13

quote:
Originally posted by asm

Yes, I known this is bad table structure... But on this i need to build a report.
So, please guide can we do it in dynamic or we have write if...else
for all 12 months

Are you using a reporting tool? You can not create a report whose datasource is from dynamic sql

Madhivanan

Failing to plan is Planning to fail

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-02-15 : 10:46:52

If you really can't change the structure, I'd create a view that looks like a better structure would, the write queries referencing that:


CREATE VIEW VwProjectCosts AS(

	SELECT
		ProjectCode,--what ever your primary key is for testprojects table
		CostDate='2010-01-01',	
		Cost=January	--guessing column name and guessing it stores costs as you haven't said
	FROM
		TestProject 
	UNION ALL
	SELECT
		ProjectCode,--what ever your primary key is for testprojects table
		CostDate='2010-02-01',
		Cost=Febuary
	FROM
		TestProject
	UNION ALL
	--etc
	--guessing you might different tables for different years too. Also include them in this.
)

To save myself typing it all out, I'd create a sproc that creates the view using dynamic sql. Then at least all the queries you write based on that will follow good practices. When something's been done badly and I have to use it, I try to do this kind approach then if the structure does ever get fixed, the queries based on this kind of fix will hardly have to be changed.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-15 : 11:12:08

quote:
Originally posted by asm

I want to pass the column name in procedure as parameter, Can any one help how to do it..

Please learn to program! Please read a book on basic software engineering. This is not just bad SQL, it is FUNDAMENTALLY bad programming in any language.

This is what we call a "Squids, automobiles, and Lady GaGa" procedure. You have no idea what it will do until you execute it.

A good module in any language has one and only one entry and one and only one exit point, and it does one and only one task. This is called strong cohesion with low coupling.

Your whole approach to programming is wrong. Please stop coding until
you learn how.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources