I am creating a dynamic sql statement in a stored procedure.
The stored procedure has table name passed in.
I cannot pass in the table name as a parameter.
How do I pass the table name as a paramter is a dynamic sql statement
I am executing a stored procedure from within other procedure with EXEC SPname Command. I have read that we should use sp_ExecuteSql system stored procedure in place of EXEC command because it will catch the execution plan whereas executing a statement or a stored procedure will not catch the execution plan. Now i am trying to execute my stored procedure as
Execute Sp_ExecuteSql @parameterName from within another stored procedure
where @paramter is an integer (but internet says that sp_executesql only accepts nvarchar/ntext datatype).So i am not able to really execute my stored procedure with sp_ExecuteSql. Am i missing out something..is there some prodedure to do this task???
ANY HELP WILL BE GREATLY APPRECIATED.
THANKS
In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)AS DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU DECLARE @Count int DECLARE @ItemNumSKU varchar(50) SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber' EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1 IF (@Count = 0) BEGIN SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber' EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT SET @ItemNumber1 = @ItemNumSKU END
-- Creating table variable to have values from 1 to 12 DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint) DECLARE @Cnt INT SET @Cnt = 1 WHILE(@Cnt <= 12) BEGIN INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0) SET @Cnt = @Cnt + 1 END
--Joining query result with the table variable to get required result DECLARE @Status1 Char(1) SET @Status1 = 'C'
SET @SQL = N'SELECT' SET @SQL = @SQL + N' MS.MonthNumber,' SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,' SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,' SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan' SET @SQL = @SQL + N' FROM' SET @SQL = @SQL + N' (SELECT ' SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#' SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost' SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice' SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan ' SET @SQL = @SQL + N' FROM ' SET @SQL = @SQL + N' Invoice_Totals ' SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number ' SET @SQL = @SQL + N' WHERE ' SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) ' SET @SQL = @SQL + N' AND ItemNum = @ItemNumber' SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year' SET @SQL = @SQL + N' GROUP BY' SET @SQL = @SQL + N' DATEPART(mm, DateTime)' SET @SQL = @SQL + N' ) Temp ' SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#' SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END
-- Creating table variable to have values from 1 to 12
DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END
--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'
SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies View RelatedI used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?
View 1 Replies View RelatedHello All,
i am using
execute sp_executesql @strQuery statement in my SP.
and My is @strQuery = "Insert into @tmp_tbl select ...". I have created @tmp_tbl using "
DECLARE @tmp_tbl TABLE(rownum ...... ".
its giving me error "
Must declare the table variable "@tmp_tbl". while executing : execute sp_executesql @strQuery statement.
How to pass that table variable with executesql ??
As i understand, i created table @tmp_tbl in my SP and trying to access it inside system SP sp_executesql .
so its not working.
Is there a way to pass table variable as parameter to sp_executesql ??
Thanks in advance. its very urgent for me..
Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
I would like to pass the name of a table in my update query. It goes something like :
set @Table = 'TableA'
UPDATE
@Table
SET
Company_id = @Company_id
However I get a syntax error when I try to pass the table name like this.
Hi there,
I have this problem..
I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....
Can someone help me??
Thanks!!
Hi there..
I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name.
MS SQL is not accepting it because it is asking me to declare it... can someone help me??
Thankyou
"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0)
AS
BEGIN
DECLARE @TB varchar(30)
SET @TB = @tablename
DECLARE @SR decimal(9,0)
SET @SR= (
SELECT MAX(SysRef)+1
FROM @tablename )
RETURN(@SR)
END
"
Hi,
I've got following code to sync two identical tables between servers.
I first create a #temp table and truncate it so that I'm left with just structure. Next thing I want to do is insert the result of a subquery into the #temp table for later use but for some reason the #temp table remains empty...
As you can see below I've tried 2 approaches to get the result in the #temp table but without success...
Can someone help me coz its getting rather annoying...
thx in advance
Code:
/***** set instance to query from *****/
declare @frominstance varchar(30)
set @frominstance = 'SERVER1INSTANCE1'
/***************************************/
declare @SQL nvarchar(4000)
-- create empty table to insert in next step
select * into #temp from tableTest where id = '1'
truncate table #temp
-- execute query to insert values from SERVER1 into table #temp
set @SQL=N'select * into #temp from ['+@frominstance+'].dbTest.dbo.tableTest tbl where tbl.id not in (select id from tableTest)'
exec sp_executesql @SQL
-- ALTERNATIVE FOR PREVIOUS execute query to insert values from SERVER1 into table #temp
set @SQL=N'select * from ['+@frominstance+'].dbTest.dbo.tableTest tbl where tbl.id not in (select id from tableTest)'
insert into #temp
exec sp_executesql @SQL
select * from #temp -- test to see what in the #temp table
drop table #temp
Hi all.
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
All ideas are welcome. Thanks.
Good Day guys, sorry i'm just new in SQL Server.
My problem is like this:
I want to pass Table Object to a declared variable and make View.
Usually we make like this:
"Select * from Table1"
I want like this:
"Select * from @Table1"
I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
I want to pass the table name as a variable
Declare @tbl varchar(30)
set @tbl = 'DB.dbo.Set'
declare @sql nvarchar(200)
set @sql =
'select * from OPENXML(@xmldoc,'DB.dbo.Site',2) with' + @tbl + ''
exec(@sql)
Please help me with the correct syntax.
Mayank
I know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou
View 2 Replies View RelatedThis one is interesting...Is there any way to pass a joined parameter into a UDF as I'm attempting below?I have a temp table that I'm trying to create:create table #t3(bmkPerson int primary key,LangCode nchar(5),SName varchar(1000))insert into #t3select t2.bmkPerson, t2.LangCode,select SName from SName_trans_udf(t2.LangCode)from #t2 t2Thanks in advance,RenaeJoin Bytes!
View 1 Replies View Related
We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.
We are trying with IRowsetIndex:: Seek method to access the data from the database.
We are facing problem when we try to access data from the table having composite index other than the first column.
For example:
The table ITEM contains the following columns:
1. ItemCode
2. PcNo
3. SubPcNo
4. BrandNo
5. DescText
6. ProductionKind
7. ProductionState
8. ClearingState
9. ST
10. TS
I€™ve two indexes for this table where the two indexes are
1. ItemKey1 on ITEM (ItemCode).
2. ItemKey2 on ITEM (PcNo, SubPcNo, BrandNo).
If I mention ItemKey1 as the index then the Seek method works perfectly.
But if I mention ItemKey2 as the index, then I€™m getting the error as €œDB_E_BADBINDINFO€?.
When we visted the MSDN for this problem we noted that:
1. When you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.
2. When passing key values to an index rowset, the consumer performs these actions only for the number of key columns specified in the cKeyValues argument in IRowsetIndex:: Seek.
So we had the composite index such that columns of a composite index are physically aligned next to each other as in the table.
When the first column of the index is the first column of the table as in index ItemKey1 we found that the seek method works perfectly.
If we pass the index ItemKey2, we are again getting the error €œDB_E_BADBINDINFO€?.
What else have we missed out while passing the keys in IRowsetIndex:: Seek?
Thanks in advance.
Regards,
Sasi.
I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this. Any help would be greatly appericated.Thanks,Ryan.
View 6 Replies View Related It's my code: 1 CREATE PROCEDURE SearchFunction2 @SQ nvarchar(30),3 @pType nvarchar(11),4 @pCol nvarchar(11)5 AS6 BEGIN7 SELECT * FROM [data] 8 WHERE ([type] LIKE '%'+@pType+'%') AND (@pCol LIKE '%'+@SQ+'%')9 END
10 GOWhen I replace '@pCol' with 'nameCol' it works fine, but when i pass it trough parameter in my aspx page [pCol.Value='nameCol'] it does not work!
hai <br>
<p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p>
CREATE PROCEDURE outputtable @tablename nchar(10) AS<br>
begin<br>
.........................<br>
insert into @tablename (classno) values (@cno)<br>
.................................<br>
END<br>
<p> I get the following error message :</p>
Msg 1087, Level 15, State 2, Procedure................<br>
Must declare the table variable "@tablename".<br>
<p>
Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
Is this possible?
I know this doesn't work but I'll post it anyways so you can see what I'm trying to accomplish.
CREATE PROCEDURE GetStuff
@something int,
@tablename varchar(50)
AS
SELECT * FROM @tablename WHERE something = @something
Like I say, I know it doesn't work...but does anyone know how to accomplish something like this?
I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".
Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)
[Code] ....
ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY]
@STATE varchar(50),@TBLNAME varchar(50)
AS
BEGIN TRANSACTION -- Start the transaction
TRUNCATE TABLE @TBLNAME;
SELECT
t1.Date_Taken as 'DATE', t1.Time as 'TIME',
t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL'
FROM dbo.SEL t1 INNER JOIN dbo.station_info t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.STATE=@STATE
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time
-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT -- Success! Commit the transaction
Error said Incorrect syntax near '@TBLNAME'.
Hi everyone,
Is that possible to passing a table as an input to Stored Procedure?
Thanks in advance
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint
There is a WETPAINT table:
ItemNo int,
Color varchar(20)
There is a DRYPAINT table:
ItemNo int,
Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)
)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)
View 15 Replies View RelatedI have a function which accepts table parmeter. i have created a view and i need to use this function.
How can i do it. how can i pass dim table into the @table variable
select * from dim cross apply fnc_user(@table variable)
can i pass the name of the table and the "order by" column name to stored procedure?
i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.
Select * from @Passedtablename. This won't work. Any insights.
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,