New to T-SQl - dynamic sql question

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

Transact-SQL (2000)

New to T-SQl - dynamic sql question

Author

Topic

sunnyjassal
Starting Member

28 Posts

Posted - 2004-03-31 : 22:22:45

Hello -

I am new to T-SQL but have done PL-SQL ... what I am trying to do requires me to write a dynamic sql ..... lets say i have 5 parameters .. ranges form int to float to varchar... depending on them i want to write a sql statement

basically if they are null then i dont want to add them to the sql where clause

first question how would i pass null to the procedure
would a call to it just be procedure_name(null, 'somevalue', 1.1, null) be sufficient? Secondly does t-sql allow ints and floats to be null?

basically what i want to do is take the dynamic sql execute it and take the result and insert it into a table. and still return the result to asp.

for instance..

sql = 'select * from tablename';
if age <> null or name <> null or price <> null or location <> null then
sql = sql + ' where '

if age <> null
sql = sql + ' age=' + age

end if
if name <> null
if age <> null then
sql = sql + ' and name=''' + name + '''
else
sql = sql + ' name=''' + '''
end if
// and so on
end if
after this i want to execute the statement
and insert it into a table

i was thinking something like
insert into sometable values(resultset goes here, i would assume there would be usage of the exec command that i have read about)

or even want to try something like selecting a particular column out of the result set and inserting into the table..

and after this still return the resultset out to of the procedure

how would i do all this in t-sql??

Thank you in advance for your help.

Any code that i can look at would be helpful also

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-31 : 22:38:01

You don't need dynamic SQL for this. Dynamic SQL should be avoided if at all possible.

Here's an example for you.

CREATE PROCEDURE djl_test_52

@ClientProcessID INT = NULL,
@ApplicationName NVARCHAR(55) = NULL,
@CPU INT = NULL

AS

SELECT
t.RowNumber,
t.EventClass,
t.TextData,
t.NTUserName,
t.ClientProcessID,
t.ApplicationName,
t.LoginName,
t.SPID,
t.Duration,
t.StartTime,
t.Reads,
t.Writes,
t.CPU
FROM
[52] AS t
WHERE
(@ClientProcessID IS NULL OR t.ClientProcessID = @ClientProcessID)
AND (@ApplicationName IS NULL OR t.ApplicationName LIKE @ApplicationName + '%')
AND (@CPU IS NULL OR t.CPU >= @CPU)

EXEC djl_test_52 --Returns all records
EXEC djl_test_52 '14840' --Returns only one process ID.
EXEC djl_test_52 NULL,'SQL Query Analyzer' --Returns only one process ID.
EXEC djl_test_52 NULL,NULL,'20' --Returns only processes with CPU over 20%.
EXEC djl_test_52 '14840','SQL Query Analyzer','70' --All criteria must be met.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sunnyjassal
Starting Member

28 Posts

Posted - 2004-03-31 : 23:19:58

thank you so much.. going to try it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources