Mazharuddin,

Thank you for the contribution.

Thank you,
The MSSQLTips.com Team

Hi,

I have a question regarding dynamic sql.

What would be difference between the 2 query

declare @script nvarchar(1000),
             @companyid int,
             @area tinyint

select comapnyid = 1 , @area = 1

select @script = 'select contactname , address, etc'+
                    + 'from tbljcontactstable' + convert(varchar(4) , @companyid)
                    + 'WHERE contact_area = ' +convert(varchar(4) , @area)

exec(@script)

AND

declare @script nvarchar(1000),
             @companyid int,
             @area tinyint

select comapnyid = 1 , @area = 1

SELECT @script = ''
SELECT @script = @script + 'select contactname , address, etc'
select @script = @script +  'from tbljcontactstable<comapnyid>'
select @script = @script +  'WHERE contact_area = <area>'
SELECT @script = REPLACE(@script, '<comapnyid>' , @companyid)
SELECT @script = REPLACE(@script, '<area>', @area)

exec(@script)

I need to develop a "generic" statement that works in various databases.  Let's say there are three DBs for each of our branch offices, namely HAMMOND, ROCKVILLE, and RIDGEMOUNT.  Each DB has the same set of table names, e.g. dbo.PERSON and same field names, e.g. LAST_NAME, FIRST_NAME, POSTAL_CODE.  There is a fourth DB where all stored procedures are housed, e.g. HQIntegration.  My stored procedure has to allow user of the branch office to grab the data pertaining to the branch location

For user in Hammond ... 

SELECT LAST_NAME, FIRST_NAME FROM HAMMOND.dbo.PERSON WHERE POSTAL_CODE = '12345'

For user in ROCKVILLE ...

SELECT LAST_NAME, FIRST_NAME FROM ROCKVILLE.dbo.PERSON WHERE POSTAL_CODE = '98765'

I wrote:

DECLARE @DBName varchar(10)

DECLARE @POSTALCODE varchar(20)

DECLARE @SQL varchar(max)

SET @SQL = '

SELECT LAST_NAME

, FIRST_NAME

FROM '+@DBName+'.dbo.PEROSN

WHERE POSTAL_CODE = '''+@POSTALCODE+''''

EXEC (@SQL)

Hey Lillian,

Why don't you just create a subquery

e.g.

DECLARE @PostalCode varchar(20)

SELECT Last_Name, FirstName

FROM (SELECT Last_Name, First_Name FROM HAMMOND.dbo.PERSON

UNION ALL

SELECT Last_Name, First_Name FROM RIDGEMOUNT.dbo.PERSON

UNION ALL

SELECT Last_Name, First_Name FROM ROCKVILLE.dbo.PERSON

) DRV WHERE POSTAL_CODE = @PostalCode

Hi

I wisht to fetch out the total record count from the Table. The following syntax gives me error. Can some one help me on the same.

Declare @TableName Varchar(100)

Set @TableName = 'TableName'
Declare @Count int
Declare @SqlString Nvarchar(1000)

Set @SqlString = 'Select @OutCount = Count(*) From ' +@TableName
 
Exec sp_Execute @SqlString, N'@OutCount Int Output', @OutCount = @Count Output

@Vishal - what are you trying to do with this code? 

Did you try to change sp_execute with sp_executesql?

How to build an sql query in fly?

I have 4 textbox firstname, middlename, lastname and city. User will enter data in any of the four textbox during runtime. So the problem is, on submit I have to build an sql query during run time for my asp.net application to search for records in my Database only for the entries which the user has eneterd.

Thanks.

Here is my contribution:

Always remember that anything called by EXEC statement is executed in a separated session. So you can't use:

EXEC 'SELECT * INTO #TMP FROM USERS'

And then call SELECT * FROM #TMP. To do so, you must create a global temporary table:

EXEC 'SELECT * INTO ##TMP FROM USERS'

That's it. =)

@Roberto - this isn't exactly true.  If you create the Temp Table first and then select data into it using EXEC you can then use SELECT to read the data.

Here is an example:

CREATE TABLE #temp (
  [name] [sysname] NOT NULL,
  [object_id] [int] NOT NULL  )

EXEC ('INSERT INTO #temp
SELECT name, object_id FROM sys.objects')

EXEC ('SELECT * FROM #temp')

SELECT * FROM #temp

i want to execute this SQL command:select * from CountryName where countryName like 's%'

 using exec()

****Please tell me solution

lets say i have written a stored procedure.Later i realized that some of keywords within the stored proc are in upper case and some in lower case,now to give it a standard look i want to change all the lowercase keywords into uppercase.For that i need a query or stored proc.I was trying but couldn't find out how to get all the keywords used within a stored proc.Would be very thankfull if you could help me... :-)

Pratibha,

Have you tried this tool?

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

Thank you,
Jeremy Kadlec
Community Co-Leader

I usually write queries whose ouptput itself is a query.Is there a way to execute the ouptut of the query without copy pasting and runing it?

Hi,

I have a table in ehich column having some dml commands.

Could please tell me how to execute these commands in sql server.

Please have look on snap shot attached

@Manish Kumar - here is simple code to do this:

create table #temp (sqlcommand varchar(500))
insert into #temp
select 'drop table  AccountID_55406' union all
select 'drop table  Accountid_70625'

DECLARE @sqlcommand varchar(500)

DECLARE db_cursor CURSOR FOR 
SELECT sqlcommand FROM #temp ORDER BY 1

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sqlcommand  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       PRINT @sqlcommand
       EXEC (@sqlcommand)
      
       FETCH NEXT FROM db_cursor INTO @sqlcommand 
      
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

I can execute my dynamic SQL statement, but when I use it in a stored procedure, I can't get at the data. As a simple example, when I run the following in a query window, it returns a set of data:

EXECUTE(N'SELECT Mox FROM dbo.MoYrTmp')

But when I put the same statement in a stored procedure and try to return the set of data, calling the stored procedure just gives me:

Command(s) completed successfully.

How do I get the stored procedure to return the result set from the dynamic query?

I don't know how, but the Execute statement is now working. Please disregard my previous post.

Hi,

I want to store the result of a dynamic query into a variable, assuming the query returns only 1 value.

Everywhere it tell me to store the result into a temp table and then query the temp table to store the value into a variable.

Can anybody please help me if there is any easier way to directly put the result into a variable, just like how mysql lets you with keyword into @variable in its dynamic query.

I have a Dynamic select, I want to choose dynamically the columns of table 2 who have names as a month but I dont want to use the complete name when I call them with SSRS, my question is how to save the results of this Dynamic Select in Table 2?I can not do it can someone help me.

Declare @Month Int = 1
Declare @test2 Nvarchar(255) =''

Set @test2 = @Month
Select @test2 = (Case @test2
When 1 then 'December'
When 2 then 'January'
When 3 then 'February'
When 4 then 'March'
When 5 then 'April'
When 6 then 'May'
When 7 then 'June'
When 8 then 'July'
When 9 then 'August'
When 10 then 'September'
When 11 then 'October'
When 12 then 'November'
else
NULL
 end )
 
 
Declare @test1 Nvarchar(255) = @Test2+'_AvgNetP'
Declare @test3 Nvarchar(255) = @Test2+'_AvgROS'
Declare @Select nvarchar(1000) 

 Set @Select =
 
 'Select Hdl_Nr,' +@test1+','+@test3+
 ' from [Table1] as T
 
Update Table2
set Table2.ROS_S = (Select @test1 from @Select)
where Table2.Hdl_Nr = T.Hdl_Nr) '

exec SP_EXECUTESQL @Select

@Francisco - try something like this.  Not sure if this is exactly what you need to do or not.

Declare @Month Int = 1
Declare @test2 Nvarchar(255) =''

set @test2 = @Month
Select @test2 = (Case @test2
When 1 then 'December'
When 2 then 'January'
When 3 then 'February'
When 4 then 'March'
When 5 then 'April'
When 6 then 'May'
When 7 then 'June'
When 8 then 'July'
When 9 then 'August'
When 10 then 'September'
When 11 then 'October'
When 12 then 'November'
else
NULL
 end )
 
 
Declare @test1 Nvarchar(255) = @Test2+'_AvgNetP'
Declare @test3 Nvarchar(255) = @Test2+'_AvgROS'
Declare @Select nvarchar(1000)
Declare @Select2 nvarchar(1000)

Set @Select = 'Select Hdl_Nr,' + @test1 + ',' + @test3 + ' from [Table1] as T'
print @select

set @Select2 = 'update t2 set t2.ROS_S = t1.' + @test1 + ' from Table2 t2 inner join Table1 t1 on t1.Hdl_Nr = t2.Hdl_Nr'
print @select2
exec (@Select2)

@Mani - the reason that the @city variable is declared twice is because it is used outsite of the sp_executesql and also within the sp_executesql.  It is a little confusing that I used the same name twice.  Hopefully that helps answer your question.

Is there anyway to see the actual SQL state being created with the parameters actually substituted.

I'm not getting the results I expected and cant tell what the problem is

Thanks,

Richard