July 3, 2018 at 2:34 am
Hi All,
Noticed a strange issue . When I query data from a table having email address field the where condition that has the email address , works fine when run directly but the same when run through an SP , the comparisons are wrong.
I've an SP that has @Email as an argument . The SP should through a message by checking if the email address already exists or not. When this is done by calling from SP it gives wrong results. Like I've an EMail which is not there in the table and the SP should check whether this already exists or not. The SP says its already there in the table where as its not. If the same SELECT statement if run directly , returns zero rows. I understand this is because SQL server uses @ variable declaration , and having @ in the data is causing problem. The problem is only when its done through SP. Thank you in advance.
Thanks....Arshad
July 3, 2018 at 3:23 am
Can you provide an example of you making the direct query, and an invocation to the stored procedure?
And, if possible, the definition of the stored procedure?
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 3, 2018 at 3:47 am
I've done a simple script, just to double check, and I can't replicate the issue (see below). Tom is right, we need the DDL here really, and some sample data that shows the issue.
Working sample:USE Sandbox;
GO
--Sample table and data
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1), Email varchar(255));
INSERT INTO dbo.SomeTable (Email)
VALUES ('ThomA@ssc.com'),('TomR@SSC.com'),('Arsh@ssc.com');
GO
--Sample Select
DECLARE @Email varchar(255);
SET @Email = 'ThomA@ssc.com'
SELECT *
FROM dbo.SomeTable
WHERE Email = @Email;
GO
--Sample Proc
CREATE PROC dbo.SomeProc @Email varchar(255) AS
SELECT *
FROM dbo.SomeTable
WHERE Email = @Email;
GO
--USe proc
EXEC dbo.SomeProc @Email = 'Arsh@ssc.com';
GO
--Clean up
DROP PROC dbo.SomeProc;
DROP TABLE dbo.SomeTable;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 3, 2018 at 4:38 am
This is the proc code giving the issue :
USE [DB2]
GO
/******Object: StoredProcedure[dbo].[PR_SaveUser_UsingIf-and-Print] Script Date: 03-Jul-18 10:50:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PR_SaveUser_UsingIf-and-Print2]
(
@EMailVARCHAR(100)
)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT EMail FROM DB1.schema1.Tab1 WHERE [EMail] = @Email)
-- Custom Error Message
PRINT 'User Already Exists';
RETURN 1;
END;
GO
Calling Code
---------------
Exec [DB2].[dbo].[PR_SaveUser_UsingIf-and-Print2] 'emp1@abc.com'
This procedure is in DB2 invoked by a user , User1 in from DB1. This does an insert into the table above in DB1. The select returns 0 nothing,which is correct, when run directly in DB1 but when the user invokes the SP from DB1 the control goes to the PRINT statement , which is wrong as the provided email ID doesn't exist in the table.
Thanks...Arshad
July 3, 2018 at 5:47 am
We
Arsh - Tuesday, July 3, 2018 4:38 AMHi Thom A and Thom R,
The objects provided you works well. I simulated this in our scenario of invoking the SP from another DB after grant execute and WITH EXECUTE AS modifications but the same this for my SP doesn't work.This is the proc code giving the issue :
USE [DB2]
GO
/******Object: StoredProcedure[dbo].[PR_SaveUser_UsingIf-and-Print] Script Date: 03-Jul-18 10:50:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PR_SaveUser_UsingIf-and-Print2]
(
@EMailVARCHAR(100)
)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT EMail FROM DB1.schema1.Tab1 WHERE [EMail] = @Email)
-- Custom Error Message
PRINT 'User Already Exists';
RETURN 1;
END;
GO
Calling Code
---------------Exec [DB2].[dbo].[PR_SaveUser_UsingIf-and-Print2] 'emp1@abc.com'
This procedure is in DB2 invoked by a user , User1 in from DB1. This does an insert into the table above in DB1. The select returns 0 nothing,which is correct, when run directly in DB1 but when the user invokes the SP from DB1 the control goes to the PRINT statement , which is wrong as the provided email ID doesn't exist in the table.
Thanks...Arshad
If I change your code to reference my sample table, is works as expected. Using the following:EXEC dbo.[PR_SaveUser_UsingIf-and-Print2] @EMail = 'ThomA@ssc.com';
EXEC dbo.[PR_SaveUser_UsingIf-and-Print2] @EMail = 'steve@SSC.com';
Returns (in the messages)User Already Exists
(Note the message only appears once, as Steve isn't in the sample data).
Can you please provide sample data that demonstrates your SP not working as you expect? Also, what are you expecting?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 4, 2018 at 7:02 am
Could it be that the Email column in DB1 is shorter than the email address you're checking and it's being truncated?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply