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
Solved

Stored Procedure MSSQL return values error

Posted on 2013-11-19
8
412 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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