February 15, 2011 at 1:33 am
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
February 15, 2011 at 1:42 am
Rather than guess, what error does it give ?
February 15, 2011 at 1:45 am
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near 'SP_EXECUTESQL'.
Patel Mohamad
February 15, 2011 at 1:53 am
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.
February 15, 2011 at 2:04 am
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
February 18, 2011 at 12:57 am
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
February 18, 2011 at 3:59 am
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
March 3, 2012 at 2:53 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply