July 20, 2010 at 1:06 am
hi,i want to select my data from table using where clause.then i want to use if else condition to set the output parameter value as 1 else 0.so that i can call this in my asp.net code.i want to do this using if else condition only.logic will be like:
select * from table where roll = @roll
if (selected)//what condition can i write here
{
set @RESULT =1//@RESULT is OUPUT Parameter
}
else
{
set @RESULT = 0
}
please help.
i have written this code,is it write?it compiles successfully but can i call this @RESULT in my asp.net.
ALTER PROCEDURE [dbo].[Students]
@id INT,
@name VARCHAR(20),
@lastname VARCHAR(20),
@roll INT,
@class NVARCHAR(10),
@r INT OUTPUT,
@RESULT INT OUTPUT
AS
BEGIN
SELECT * FROM SchoolStudents WHERE roll = @roll
SELECT @r = COUNT(*) FROM SchoolStudents WHERE roll = @roll
IF @r != NULL
BEGIN
SET @RESULT = 1
END
ELSE
BEGIN
SET @RESULT = 0
END
END
Yah,COUNT(*) will never return NULL.Sorry!! plz correct this. I think there is no need of this @r variable.but iam not understanding what condition i can put to get SET @RESULT =1 in case select query is compiled succesfully otherwise SET @RESULT =0.how can i do this.:crying:
plz modify this code or tell me am i going in right direction or i have to do it in other way.
THANKS FOR the Help.Now i understand how to do it.:-)
July 20, 2010 at 1:41 am
U can use case conditional expression in ur qry... for more help see BOL...
July 20, 2010 at 2:31 am
Hi,
You can write like this:
If(your condition)
SET @result=1
ELSE
SET @result=0
OR
IF(your condition)
SELECT @result=1
ELSE
SELECT @result=0
July 20, 2010 at 5:44 am
i don't think your @r variable will ever be null.... count would return zero if nothing was found;
SELECT @r = COUNT(*) FROM SchoolStudents WHERE roll = @roll
--will return a non null integer, 0 or more.
can you explain what you wanted? Were you trying to return 1 if nothing was found? what were you trying to accomplish?
proof of concept:
declare @r int
SELECT @r = COUNT(*) FROM sys.tables WHERE name = 'bananas'
print @r
--returns 0
Lowell
July 20, 2010 at 6:38 am
hi,i want to select my data from table using where clause.then i want to use if else condition to set the output parameter value as 1 else 0.so that i can call this in my asp.net code.
Correct me if i am wrong - You need to get a datatable with all records that match the value of @roll. If there is at least one matching record, you want to set @output = 1, otherwise set @output = 0.
Assuming that this is what you need, you will have to do the following.
select * from SchoolStudents where roll = @roll
if exists(select * from SchoolStudents where roll = @roll) set @result = 1
else set @result = 0
However, you can check if any row exists in your datatable from your business layer. So, you will not need the output parameter in that case.
Being more specific in your question will get you an answer sooner.
In addition, you may want to read this - http://msdn.microsoft.com/en-us/library/ms188048%28SQL.90%29.aspx
- arjun
https://sqlroadie.com/
July 20, 2010 at 7:45 am
Using 'Select *' is not a good thing and shouldn't be used. You should take the time to type in the field names
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2010 at 11:48 pm
Mike do you mean to say that the following select statements are not the same?
create table #table(col1 int, col2 int)
select * from #table
select col1, col2 from #table
If so, please explain.
- arjun
https://sqlroadie.com/
July 21, 2010 at 12:07 am
thanks a lot for ur response sir.i got it.now my doubts are clear.
July 21, 2010 at 6:31 am
Arjun Sivadasan (7/20/2010)
Mike do you mean to say that the following select statements are not the same?create table #table(col1 int, col2 int)
select * from #table
select col1, col2 from #table
If so, please explain.
- arjun
Arjun,
no, what I am saying is that it is not good practice to use select *. Although the above will return the same results, the field names should be used instead of select *. I've seen code where there is a working table that is populated by select *. When a new field was added to the source table, this statement errors out. So, to use your example.
create table SourceTable(col1 int, col2 int)
create table TargetTable(col1 int, col2 int)
-- This will work
insert into TargetTable (col1, col2 )
select * from SourceTable
-- but now it won't
Alter table SourceTable add col3 int
insert into TargetTable (col1, col2 )
select * from SourceTable
-- where as this will always work regardless
insert into TargetTable (col1, col2 )
select col1, col2 from SourceTable
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 6:45 am
Hmm i see. I agree that it is more readable if you list the columns. In the specific case that you have mentioned, it is always better to list the fields. Anyway, I never use * to select columns, simply because there are always some columns in the table that i don't need at application level; such as EntryDate and EntryBy.
Thanks for taking the time out Mike. I would appreciate it if you explain the 'why' of things you post, as there are a lot of newbies around. Explaining the reasons will avoid misconceptions and as you should know, a simple misconception can take people a long way in the wrong direction.
- arjun
https://sqlroadie.com/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply