Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure MSSQL return values error

Posted on 2013-11-19
8
Medium Priority
?
419 Views
Last Modified: 2013-11-21
This stored procedure should return true if record count greater then 0.


USE [ASSETMNT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[myTest]
@UserEmail varchar(50),
@IsSuperUser INT


as 
begin 

SET NOCOUNT ON;

	DECLARE @esql NVARCHAR(max)
	DECLARE @ssql NVARCHAR(max)
	DECLARE @EBOO VARCHAR(8) 
	DECLARE @SBOO VARCHAR(8) 
	DECLARE @ECOUNT VARCHAR(8) 
	DECLARE @SCOUNT VARCHAR(8)
	SET @EBOO = ('FALSE')
	SET @SBOO = ('FALSE')

SET @esql = 'SELECT COUNT(UEMAIL) =' +  @ECOUNT + '  FROM  TBL_USERS WHERE UEMAIL =' +@UserEmail
	
SET @ssql = 'SELECT COUNT(SuperUser) ='+@SCOUNT+' FROM TBL_USERS WHERE 
SuperUser = 1'

	IF    @SCOUNT > 0
	SET @EBOO = ('TRUE')


	IF   @ECOUNT > 0
	SET @EBOO = ('TRUE')

	exec sp_executesql @esql,@ssql

return 1

end

Open in new window

0
Comment
Question by:xeondxb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39659009
Try this:
USE [ASSETMNT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[myTest]
@UserEmail varchar(50),
@IsSuperUser INT
as 
begin 

    SET NOCOUNT ON;

    DECLARE @xsql NVARCHAR(max);

    SET @xsql = 'SELECT case when IsNull(COUNT(UEMAIL), 0) > 0 then 1 else 0 end as cnt FROM TBL_USERS WHERE UEMAIL = ''' + @UserEmail + ''' AND (SuperUser = 1 OR ' + @IsSuperUser + ' <> 1)';

    exec sp_executesql @xsql;

end

Open in new window

0
 

Author Comment

by:xeondxb
ID: 39659053
thanks for your quick reply, im getting this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'UEMAIL'.


could you please explain a little about this script.
what your doing in this script because im really new in SQL programming it will help me in future ...

Thanks
0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 400 total points
ID: 39659183
1.What is the structure of TBL_USERS?
2.What values are you passing to the SP?

Comments in SQL:
USE [ASSETMNT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[myTest]
@UserEmail varchar(50),
@IsSuperUser INT
as 
begin 

    SET NOCOUNT ON;

    DECLARE @xsql NVARCHAR(max);

    SET @xsql = 'SELECT case when IsNull(COUNT(UEMAIL), 0) > 0 then 1 else 0 end as cnt FROM TBL_USERS WHERE UEMAIL = ''' + @UserEmail + ''' AND (SuperUser = 1 OR ' + @IsSuperUser + ' <> 1)';
    --case when IsNull(COUNT(UEMAIL), 0) > 0 then 1 else 0 end
    /*
		CASE:	You want to return 0 when there is no record and 1 when there is one (or more)
		IsNull: If the result of argument 1 is NULL, return argument 2
    */

    --WHERE UEMAIL = ''' + @UserEmail + ''' 
    /*
		'':	You need to surround text with single quotes - to insert the literal, you need to double it up.
    */
    
    --AND (SuperUser = 1 OR ' + @IsSuperUser + ' <> 1)
    /*
		Check for the SuperUser field being equal to 1 if @IsSuperUser argument is set;
		So, if @IsSuperUser = 1; AND (SuperUser = 1 OR 1 <> 1)
		    if @IsSuperUser = 0; AND (SuperUser = 1 OR 0 <> 1)
		
		Maybe easier if it was AND (SuperUser = 1 OR ' + @IsSuperUser + ' = 0)
		So, if @IsSuperUser = 0, the statement would be true regardless of the TBL_USERS.Super_User column.
    */

    exec sp_executesql @xsql;

end

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

Expert Comment

by:_agx_
ID: 39659679
(no points.... just a note about security)

> exec sp_executesql @xsql;

Guys - why use dynamic SQL here? Unless I missed something, I don't think it's even needed?

Also you want to be very careful using dynamic sql because it can expose your db to sql injection, if you're not careful.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 1600 total points
ID: 39659954
EDIT: Added cfproc example

@xeondxb - If I'm understanding correctly, I think the procedure is overcomplicating things .... Seems like you're simply trying to look up a user by email, and return true if they're a "super user"? If so, that doesn't require dynamic SQL, just a SELECT.  

Also,  RETURN has a different meaning than you may be thinking.  It's generally used to return a "status" (succeeded or failed w/error) - NOT return data. You typically return data via a query (ie resultset) OR an OUTPUT variable. To keep it simple for now, return a query.

This example will return 1 when the record matching the email is a super user and 0 when it's NOT.

ALTER procedure [dbo].[myTest]
@UserEmail varchar(50)
AS
BEGIN

      SET NOCOUNT ON;

         SELECT ISNULL( MAX(SuperUser), 0) AS IsSuperUser 
         FROM   TBL_USERS 
         WHERE UEMAIL = + @UserEmail
         AND     SuperUser = 1

END

Open in new window


Just pass in the email and grab the result. Since CF can treat numbers as booleans, just use the value in your CF code like below:

<!--- not tested --->
<cfstoredproc procedure="MyTest" ....>
      <cfprocparam value="someemail@address.com" cfsqltype="cf_sql_varchar" type="IN">
      <cfprocresult name="theProcResultName" resultSet="1">
</cfstoredproc>


        <cfif theProcResultName.isSuperUser>
               this is a super user. do something
        <cfelse>
               not a super user. do something else...
        </cfif>
0
 

Author Comment

by:xeondxb
ID: 39661903
_agx_@

ur missing one thing in ur script .

when user will try to register  as a super user. Script will check email address if email address is exist in the database it will send different code. from the code i'll know email is  exist in the database, i'll show message to the user. If email does not exist in the database it'll go and check super user. Super  can be one in the system.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39662536
So you're trying to do two things:

1) Determine if the email exists
2) Determine if the user CAN register as a superuser?

   > Super  can be one in the system.

I'm not sure I understand that part.  Do you mean there can only be one super user in the system?
0
 

Author Comment

by:xeondxb
ID: 39665169
System will allow only one Super user.
In my Database, Field is Boolean.

1.) 1st system checking whether email is exists in the database or no, if yes then show message "Email is already exists"

If email not exists  then
2.) system will Check whether Super user already registered or no, if Superuser already registered then show Message "Superuser  already Registered."  ELSE register.


Thanks
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question