July 16, 2004 at 9:16 am
Hello All,
SQL Server 7
I am a newbie to Stored Procedures and need some help. I have a SP here that I can't seem to get to print when testing it. I am using the print function to test it and to see exactly how the query is being built by the SP. Here is my code...
CREATE PROCEDURE
dbo.SRS_qSelectHistoryStudentStatus
@aamc int,
@entry nvarchar (50)
AS
DECLARE @sql nvarchar(4000)
SELECT @sql =
'selectSS.*, U.username, RT.reason_type_id AS reason_type_id, RT.event_name AS reason_name, ET.event_type_id AS event_type_id, ET.event_name AS event_name
fromSTUDENT_STATUS SS, REASON_TYPES RT, EVENT_TYPES ET, USERS U
whereSS.status_changers_edcom_id = U.edcom_id
and SS.status_event_type_id = ET.event_type_id
and SS.status_reason_type_id = RT.reason_type_id'
SELECT @sql = @sql + ' and SS.aamc = '' + @aamc + '''
if @entry is null
SELECT @sql = @sql + ' and SS.status_id = '' + @entry + '''
SELECT @sql = @sql + 'order by SS.status_refresh_date DESC'
print @sql
Second questions... When I am testing this query in Query Analyzer and I hit the 'Play' button it creates the SP for me inside of the Enterprise Manager. This causes a problem if I try and test the SP again in Query Analyzer becuase it tells me that the SP has already been created. Is there a way to test the SP's without them being created???
Thanks in advance,
Josh
July 16, 2004 at 11:15 am
Second questions... When I am testing this query in Query Analyzer and I hit the 'Play' button it creates the SP for me inside of the Enterprise Manager. This causes a problem if I try and test the SP again in Query Analyzer becuase it tells me that the SP has already been created. Is there a way to test the SP's without them being created???
this will recreate your procedure with the new/changed code.
ALTER PROCEDURE dbo.SRS_qSelectHistoryStudentStatus
...
code
...
to actually execute the procedure ( or test ).
EXEC dbo.SRS_qSelectHistoryStudentStatus
And Actually I don't think that it is necessary to use dynamic sql for this proc...
Maybe:
ALTER PROCEDURE dbo.SRS_qSelectHistoryStudentStatus
@aamc int,
@entry nvarchar(50) = null
AS
select
SS.*,-- * Bad Practice...
U.username,
RT.reason_type_id AS reason_type_id,
RT.event_name AS reason_name,
ET.event_type_id AS event_type_id,
ET.event_name AS event_name
from
STUDENT_STATUS SS
JOIN REASON_TYPES RT ON SS.status_reason_type_id = RT.reason_type_id
JOIN EVENT_TYPES ET ON SS.status_event_type_id = ET.event_type_id
JOIN USERS U ON SS.status_changers_edcom_id = U.edcom_id
where
SS.aamc = @aamc
and( SS.status_id = @entry or @entry is null )-- @entry null will get all SS.status_id
order by
SS.status_refresh_date DESC
/rockmoose
You must unlearn what You have learnt
July 16, 2004 at 11:44 am
Hello Rockmoose, thank you for your help.
Thank you for poiting out the bad practice of select SS.*. I only did this because when I was building the dynamic query, I had too many columns and it would excede the variable limit.
I am not clear about the "and( SS.status_id = @entry or @entry is null)" part of the script though. I am sorry if I am reading this wrong, but if the value of @entry is NULL then I do not want that column to be used as a condition to select data.
select status_id
from table X
where status_id = NULL
is not the same as
select status_id
from table X
am I reading the SP right, in that if @entry is not defined or is null then it searches that column for null values?
July 16, 2004 at 12:21 pm
Hi Josh,
You are welcome.
and( SS.status_id = @entry or @entry is null)
if @entry is null this will always evaluate to true, thus return all the rows no matter what SS.status_id is.
if @entry is not null this will only evaluate to true if SS.status_id = @entry, thus returning only the rows with the provided @entry value.
am I reading the SP right, in that if @entry is not defined or is null then it searches that column for null values?
nope, it does not search the column for null values.
rephrase in 4 steps when @entry = null:
and( SS.status_id = @entry or @entry is null )
and( SS.status_id = null or null is null )
and( false or true )
and( true )
Hope I managed to explain the issue?
/rockmoose
You must unlearn what You have learnt
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply