Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-26 : 09:12:40
|
| Ed Cox writes "HelloIn this example from 4guys it is described how to use dynamic field and table names. I need to place the resualt of a similar agregate math quiry into a variable for use later in the SP. I'm failing bad. Please help. See my sample below rollas.Thank YouEdCREATE PROCEDURE MyProc (@TableName varchar(255), @FirstName varchar(50), @LastName varchar(50))AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement into the -- variable @SQLStatement SELECT @SQLStatement = "SELECT * FROM " + @TableName + "WHERE FirstName = '" + @FirstName + "' AND LastName = '" + @LastName + "'" -- Execute the SQL statement EXEC(@SQLStatement)myCode ********************************************************** SELECT @SQL1 = " SELECT @prodtotal = " SELECT @SQL1 = @SQL1 + " SUM(" + @pre + @datafield + ")" SELECT @SQL1 = @SQL1 + " FROM " + @datatable SELECT @SQL2 = " WHERE " + @pre + "machine_id = '" + @mach_id + "'" SELECT @SQL2 = @SQL2 + " AND " + @pre + "station_id = 0 " SELECT @SQL2 = @SQL2 + " AND " + @pre + "data_id=1 " SELECT @SQL2 = @SQL2 + " AND " + @pre + "data_date >= '" + @startdate + "'" SELECT @SQL2 = @SQL2 + " AND " + @pre + "data_date <= '" + @enddate + "'" SELECT @answer = @prodtotal * @othervariable" |
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 10:17:29
|
| wha? Can you give an example of the dml that you expect to run? You seem to set up your @SQL2 variable but then not do anything with it? You realize you need to execute it? Also, you @prodtotal variable is only going to have the scope of @SQL2 and your outside batch that populates @answer will know see @prodtotal . . . Maybe you should take a look at sp_executeSQL in BOL?Provide some ddl and an tell what what you are trying to do. Maybe there is a better way than the dynamic sql . .Jay<O> |
 |
|
|
|
|
|
SQL