Help needed to change the select statement

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

Help needed to change the select statement

Author

Topic

sravz
Starting Member

42 Posts

Posted - 2011-06-21 : 15:04:49

Hi Every one,

I am trying to write a select stored procedure which takes 2 input values Name and Field type.

So when the user enters a value in the name field and select the fieldtype(Which are the columns in the table NewRequests) the result retrieved should be based on that value

The stored procedure retrieves the result I want but I have been repeating the select statement.I am using SQL SERVER 2008.

Finally the stored procedure ends with like 200 rows.
I just want to know if there is any minimization to the following stored procedure.

CREATE PROCEDURE [dbo].[sp_RequestEntrySelect]
@nName INT= NULL,
@nField Varchar(100)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY

IF(@nField='REQUESTID' AND @nName<>'NULL')
BEGIN
SELECT [RequestID],[SERVICETYPE],[CLIENTID],[SUPPLIERID],[TSID],[AUDITCODE],[AUDITTYPE],[JOBID]
,[JOBDASH],[REQUESTEDBY],[DATEREQUESTED] ,[DATEREQUIRED],[DATESCHEDULED],[DATECOMPLETED]
,[DATEDUE],[DATEREVIEWED],[REVIEWER],[STATUS],[COMMENTSALL],[COMMENTSUTS] ,[TCSUBMITTED],[RESULTSENTERED] FROM
[New Requests]
WHERE
RequestID=@nName
END
ELSE IF(@nField='SERVICETYPE'AND @nName<>'NULL')
BEGIN
SELECT [RequestID],[SERVICETYPE],[CLIENTID],[SUPPLIERID],[TSID],[AUDITCODE],[AUDITTYPE],[JOBID]
,[JOBDASH],[REQUESTEDBY],[DATEREQUESTED] ,[DATEREQUIRED],[DATESCHEDULED],[DATECOMPLETED]
,[DATEDUE],[DATEREVIEWED],[REVIEWER],[STATUS],[COMMENTSALL],[COMMENTSUTS] ,[TCSUBMITTED],[RESULTSENTERED] FROM
[New Requests]
WHERE
SERVICETYPE=@nName
END
ELSE
BEGIN
SELECT [RequestID],[SERVICETYPE],[CLIENTID],[SUPPLIERID],[TSID],[AUDITCODE],[AUDITTYPE],[JOBID]
,[JOBDASH],[REQUESTEDBY],[DATEREQUESTED] ,[DATEREQUIRED],[DATESCHEDULED],[DATECOMPLETED]
,[DATEDUE],[DATEREVIEWED],[REVIEWER],[STATUS],[COMMENTSALL],[COMMENTSUTS] ,[TCSUBMITTED],[RESULTSENTERED] FROM
[New Requests]
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
END

Just posting the sample procedure.but the nField has more selections

Please give me some suggestions
Thanks in Advance,
Sravz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 15:48:22

What you are trying to do is a variation of the "dynamic search conditions" described in Erland Sommarskog's blog here: http://www.sommarskog.se/dyn-search-2008.html

See if the ideas in his blog would help you - if you run into problems, please reply, I am sure people on this forum would be able to offer useful suggestions.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-21 : 16:25:57

Please, please, please get a basic education in Software Engineering AND RDBMS. A module of code has high cohesion when it does one AND only one task; the highest form of cohesion is “Functional” AND the best examples are math functions. The weakest cohesion is “Coincidental”, like your nightmare. Serious SQL programmers refer to code like yours AS “Automobiles, Squids AND Lady GaGa” procedures.

In RDBMS, there is another design principle; you do not mix data and meta-data. oh, you are still confusing fields and columns, which are nothing alike. Did you really mean to say “@nname <>'NULL'” and not @nname IS NOT NULL ?

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

sravz
Starting Member

42 Posts

Posted - 2011-06-21 : 16:59:46

@ sunitabeck ----I found one solution.Thanks For the help.

@Joe CELKO.I am not a professional at SQL SERVER.Why do you want to reply to the post,When u don't understand my questions.Thanks though for ur suggestions.Please reply to the post if u can help me with the question.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-21 : 17:25:50

I cannot help you until you get a basic education. I do understand your questions; they are fundamentally wrong. You are dangerous to your employer. YOu are not finding answer; you are copying kludges.

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

sravz
Starting Member

42 Posts

Posted - 2011-06-21 : 21:15:56

JCELKO------I DONT HAVE ENOUGH TIME TO ARGUE WITH YOU.IF YOU REALLY UNDERSTAND MY QUESTION,YOU COULD HAVE TOLD ME HOW TO MAKE CORRECTIONS.I KNOW I HAD TO LEARN SQL.BUT RIGHT NOW I HAD ENOUGH WORK TO DO ON TOP OF MY HEAD.[I AM A DOT NET DEVELOPER NOT SQL DEVELOPER]

Thanks
Sravz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources