Solved

Stored Procedure MSSQL return values error

Posted on 2013-11-19
8
417 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 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
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.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

628 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