Solved

Stored Procedure MSSQL return values error

Posted on 2013-11-19
8
411 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
  • 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 100 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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