Solved

Stored Procedure MSSQL return values error

Posted on 2013-11-19
8
410 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now