Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

if Exists Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 1:33 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
Hi everybody,

this is the normal query through this we will come to know whiether the records exists or not
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN
SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
END


my questions
i want to check the record exists or not in the table then execute the query
through following syntex but it gives an error

SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN EXEC SP_EXECUTESQL @SQLQuery
END


need your assistances

thanks in advance


Patel Mohamad
Post #1064069
Posted Tuesday, February 15, 2011 1:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,949, Visits: 8,304
Rather than guess, what error does it give ?




Clear Sky SQL
My Blog
Kent user group
Post #1064071
Posted Tuesday, February 15, 2011 1:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near 'SP_EXECUTESQL'.


Patel Mohamad
Post #1064073
Posted Tuesday, February 15, 2011 1:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,949, Visits: 8,304
I suspect this is a homework question , is it ?

You will have to insert the results of the sp_executesql into a temp table and test against that.




Clear Sky SQL
My Blog
Kent user group
Post #1064077
Posted Tuesday, February 15, 2011 2:04 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
the following code illustrate you in breif actual i want the table name, respective column data from over all Tables of the Database

DECLARE @iIndexRow INT ,@RowsCount_Row int , @SQLQuery NVARCHAR(4000)
DECLARE @Value VARCHAR(100)
DECLARE @TableName VARCHAR(100) ,@ColumnName VARCHAR(100)
DECLARE @xtype int
DECLARE @ID_Row INT
DECLARE @iIndexCol INT ,@RowsCount_Col int

SELECT @Value = 'VALUE'

SELECT Identity(INT,1,1) AS Sr_No, id, Name INTO #Temp_Table_Row
FROM SYSOBJECTS
WHERE xtype = 'U' ORDER BY [Name]
SELECT @RowsCount_Row = @@RowCount
SELECT @iIndexRow = 1
WHILE @iIndexRow <= @RowsCount_Row
BEGIN --- 1.0
if EXISTS(SELECT Sr_No FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow)
BEGIN -- 1.1
SELECT @ID_Row = ID FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow
SELECT Identity(INT,1,1) AS Sr_No , id, Name , xtype INTO #Temp_Table_Col
FROM Syscolumns
where id = @ID_Row
and xtype in (35 ,98 ,165 ,167 ,173 ,175 ,231 ,231 ,239) -- ,99 For datatypes char varhcar etc
ORDER BY [Name]

SELECT @RowsCount_Col = @@RowCount
SELECT @iIndexCol = 1
WHILE @iIndexCol <= @RowsCount_Col
BEGIN --- 2.0
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN --- 2.1
SELECT @TableName = Object_Name(ID) ,@ColumnName = Name , @xtype = xtype FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + '] like% ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN --- 2.1.0
EXEC SP_EXECUTESQL @SQLQuery
END --- 2.1.0
END --- 2.1
SELECT @iIndexCol = @iIndexCol + 1
END --- 2.0
DROP TABLE #Temp_Table_Col

END -- 1.1
SELECT @iIndexRow = @iIndexRow + 1
END --- 1.0
DROP TABLE #Temp_Table_Row




Patel Mohamad
Post #1064086
Posted Friday, February 18, 2011 12:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 6:02 AM
Points: 2,242, Visits: 208
A bit complicated code, what are you trying to do?

why do you try running a function from a stored procedure after passing a value form the Exist statement
Post #1066173
Posted Friday, February 18, 2011 3:59 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
this is the normal query
through this we will come to know whiether the records exists or not in the table
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN
SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
END


my questions
i want to check the record exists or not in the table then execute the query
through following syntax but it gives an error

SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN EXEC SP_EXECUTESQL @SQLQuery
END
i am assign the query into one variable and then executing it through EXEC SP_EXECUTESQL but before executing the query i want to check the record exists for the build up query

hope you got it.....


Patel Mohamad
Post #1066249
Posted Saturday, March 3, 2012 2:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:24 AM
Points: 13, Visits: 27
Try this way..

DECLARE @CNT2 VARCHAR(100)

SET @SQLQuery = N'SELECT @CNT = COUNT(*) FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)

SP_EXECUTESQL @SQLQuery, N'@CNT INT OUT', @CNT2 OUT


if EXISTS(@CNT2>0)
BEGIN
SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)

EXEC SP_EXECUTESQL @SQLQuery
END
Post #1261112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse