Variables As Table Or Column Names In A Stored Procedure
Apr 16, 2008
I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks
View 2 Replies
ADVERTISEMENT
Nov 8, 2004
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
View 6 Replies
View Related
Apr 10, 2008
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
View 6 Replies
View Related
Sep 5, 2004
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
View 4 Replies
View Related
Jul 2, 2007
Hello all,
Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name???
Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...
Cheers,
Justin
View 4 Replies
View Related
Sep 7, 2005
I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.
View 3 Replies
View Related
Dec 16, 2005
Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi
View 5 Replies
View Related
Jan 11, 2006
I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS. I know there was a problem doing this in DTS, which would result in an Invalid Pointer error. I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider." The stored procedure runs fine on it's own.
Any thoughts?
View 24 Replies
View Related
May 5, 2000
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.
View 1 Replies
View Related
Oct 20, 2006
Hi
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column
DECLARE @value
SET @column = 'col1'
SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
thanks
View 2 Replies
View Related
Oct 9, 2007
Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases.
I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value:
Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = ['+@idpartition+'] AND StoreIdentifier IS NULL')
Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'.
If I change the last line to: WHERE [IdPartition] = 2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated.
Best regards, Tim
View 4 Replies
View Related
Sep 21, 2015
If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies
View Related
Nov 14, 2005
I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?
BTW both variables are of type char(100)
View 1 Replies
View Related
Mar 19, 2006
Is it possible to use variable name to dynamically define a query in a stored procedure? EX:
@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category
SELECT @Column
FROM @Table
View 1 Replies
View Related
Jun 22, 2006
Hi, I am new to using SQL for anything more in depth than querying and reporting.
I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?
quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)
set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'
create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);
insert into @a_tmptbl values ( "foobar", 21.76, 1);
select * from @a_tmptbl;
The error I am getting is:
quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.
Any advice would be gratefully accepted
View 3 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Dec 4, 2007
I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
exec( 'select * from ' + @table)
or just
select * from @table
Any suggestions will be appreciated.
View 10 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Dec 13, 2007
Hi All,
The script below may be use to find out what stored procedure uses a specified column from any of the table. This could be helpful in cases you have change a field name of a table and want to find out what stored procedure uses that column.
create procedure seek_sp_for_columns
@colname_para varchar(500)
as
begin
create table #temp_t
(
textcol varchar(1000)
)
create table #temp_t2
(
procname varchar(500)
)
declare @procname as varchar(500)
declare @found as int
declare @colname as varchar(500)
declare @valid_colname as int
select @valid_colname = count(id)
from syscolumns
where name = @colname_para
if (@valid_colname > 1)
begin
set @colname = '%' + @colname_para + '%'
declare sp_cursor cursor
for select name
from sysobjects
where xtype = 'P'
open sp_cursor
fetch next from sp_cursor
into @procname
while @@fetch_status = 0
begin
insert into #temp_t
exec sp_helptext @procname
set @found = 0
select @found = count(textcol)
from #temp_t
where textcol like @colname
if (@found > 0)
begin
insert #temp_t2 values(@procname)
end
delete #temp_t
fetch next from sp_cursor
into @procname
end
close sp_cursor
deallocate sp_cursor
select *
from #temp_t2
drop table #temp_t
drop table #temp_t2
end
else
begin
select 'Please verify column name'
end
end
View 2 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Sep 19, 2000
Hi!
I am kind of new to the sequel server and I have to write a stored procedure to create raw of my table. I wrote the following stored procedures, but I am not sure weather I can use variables in select statements as in the following procedure. Could you please explain what is the problem, why I am getting an error, when I am trying to create it? <The error says that there is an incorrect syntax near '@tbleName' in lines 21, 25 and 30>
Thank you very much!
Elly.
/* Inserts raw into the talble tbleName. Called from other procs.
tbleName: The name of the table to insert
other variables: values to insert
checkParentId,..: items to be checked upon-parentid, position, parent primary key
Returns:
0 : Successfully inserted a raw into the table
-1 : Position to insert is taken
-16: The specified parent does not exist
-17: Insert was unsuccessful
*/
create procedure sp_acml_createRaw (@tbleName VARCHAR(50), @nameN VARCHAR(50),
@hrefN VARCHAR(50), @posN VARCHAR(50), @parentIdN VARCHAR(50),
@nameV VARCHAR(150),@hrefV VARCHAR(255),
@posV SMALLINT, @parentIdV SMALLINT,
@checkParentId VARCHAR(50), @checkPosition VARCHAR(50),
@checkParentKey VARCHAR(50))
as
BEGIN
/* Check weather the parent exist */
IF exists (select @parentIdN
from @tbleName
where @tbleName.@checkParentKey = @parentIdV) /* if parent exist */
BEGIN
/* Check weather the isertion position is correct */
IF exists (select @checkParentId from @tbleName
where @tbleName.@checkParentId = @parentIdV AND
@tbleName.@checkPosition = @posV)
return -15 /*Insertion position is incorrect - it is taken already*/
/* Insertion postion is correct */
insert into @tbleName(@tbleName.@nameN, @tbleName.@hrefN, @tbleName.@posN, @tbleName.@parentIdN)
values (@nameV, @hrefV, @posV, @parentIdV)
IF (@@ERROR != 0)
return -17 /* Insert was unsuccessful */
return 0
END
return -16 /* The parent specified was not found */
END
/* End of sp_acml_createRaw */
View 2 Replies
View Related
Aug 25, 2004
I am writing a stored procedure that basically gets a whole lot of info. Simple enough... that part works. Now I want to add functionality to pass in sortBy and direction variables so the results can be sorted from a web page. The code I have is:
Code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure GetRecruiterApplicants
@useridvarchar(50),
@trackerGroup varchar(50),
@sortBy varchar(20),
@dir varchar(5)
as
begin
set nocount on
SELECT C.USERID INTO #VU FROM VUSERS V WITH (NOLOCK), COMPANIES C WITH (NOLOCK)
WHERE CHARINDEX(','+C.USERID+',', ','+VUSERS+',')>0 AND V.USERID=@userid
UNION SELECT @userid
SELECT distinct l.AccessCode, l.ApplicantGivenName, l.ApplicantFamilyName, l.DateCreated, l.DateApplicantAccessed, p.ApplicationTitle, l.disabled
FROM chamslinks l, chamsProjectIDs p, chamsGroups g
WHERE l.TrackerID IN (SELECT userid FROM #VU)
AND g.trackerGroup = @trackerGroup
AND p.groupTblID = g.groupTblID
AND p.ProjectID = l.ProjectID
ORDER BY l.@sortBy @dir
DROP Table #VU
set nocount off
end
The error I am getting is: "Server: Msg 170, Level 15, State 1, Procedure GetRecruiterApplicants, Line 24
Line 24: Incorrect syntax near '@dir'."
Any help?
View 5 Replies
View Related
Aug 20, 2007
i ahve the following procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create PROCEDURE testflu @p_acad_period nvarchar (5)
AS
SET NOCOUNT ON
/* Base Table fields */
DECLARE @t_student_id nvarchar (11)
DECLARE @full_time_student bit
DECLARE @student_id nvarchar (11)
DECLARE @acad_period nvarchar (5)
DECLARE @aos_code nvarchar (11)
DECLARE @aos_period nvarchar (5)
DECLARE @full_desc_static nvarchar (200)
DECLARE @full_desc_session nvarchar (200)
DECLARE @dept_code nvarchar (10)
DECLARE @aos_start_dt datetime
DECLARE @exp_length smallint
DECLARE @unit_length nvarchar (10)
DECLARE @student_year tinyint
DECLARE @hrs_per_week int
DECLARE @aos_end_dt datetime
DECLARE @no_of_weeks numeric
DECLARE @hrs_total decimal (18,2)
DECLARE @main_course_study_is_C2K tinyint
DECLARE @hrs_actual decimal (18,2)
DECLARE @hrs_actual_student decimal (18,2)
DECLARE @hrs_notional_course decimal (18,2)
DECLARE @hrs_notional_student decimal (18,2)
DECLARE @student_type nvarchar (10)
DECLARE @moa_code nvarchar (10)
DECLARE @qual_aim nvarchar (3)
DECLARE @numeric_qual_aim int
DECLARE @subject_code nvarchar (4)
DECLARE @subject_area_main nvarchar (10)
DECLARE @subject_area_group nvarchar (10)
DECLARE @course_group char (1)
DECLARE @FE_HE_type char (2)
DECLARE @nvq_lvl_ind nvarchar (1)
DECLARE @geolocn_code nvarchar (10)
DECLARE @surname nvarchar (40)
DECLARE @forename nvarchar (40)
DECLARE @birth_dt datetime
DECLARE @post_code nvarchar (8)
DECLARE @student_attend_mode nvarchar (10)
DECLARE @funding decimal (18,2)
DECLARE @key_skill_count smallint
DECLARE @additionality_yn bit
DECLARE @fee_waiver nvarchar (10)
DECLARE @start_date datetime
DECLARE @student_status nvarchar (10)
DECLARE @end_date datetime
DECLARE @spec_learn_disab nvarchar (2)
DECLARE @sldd_ind tinyint
DECLARE @age smallint
DECLARE @full_time_ind tinyint
DECLARE @age_1960_ind tinyint
DECLARE @tsn_ind tinyint
DECLARE @link_ind tinyint
DECLARE @ESOL nvarchar (3)
DECLARE @esol_ind tinyint
DECLARE @infill_ind tinyint
DECLARE @RP_entitlement tinyint
DECLARE @threshold1 datetime
DECLARE @threshold2 datetime
DECLARE @threshold3 datetime
DECLARE @student_full_cost_ind tinyint
DECLARE @fundable_ind tinyint
DECLARE @LP_entitlement tinyint
DECLARE @LP2_entitlement tinyint
DECLARE @LP3_entitlement tinyint
DECLARE @outcome nchar (10)
DECLARE @OP_entitlement tinyint
DECLARE @tenhour_ind tinyint
DECLARE @basicIT nchar (3)
DECLARE @basic_it_ind tinyint
DECLARE @special_needs_ind tinyint
DECLARE @Evening_course tinyint
DECLARE @RP_basic decimal (18,2)
DECLARE @RP_NTETS_weight decimal (18,2)
DECLARE @RP_enhance_NTETS decimal (18,2)
DECLARE @RP_enhance_Age decimal (18,2)
DECLARE @RP_enhance_TSN decimal (18,2)
DECLARE @RP_enhance_IT decimal (18,2)
DECLARE @RP_enhance_SLDD decimal (18,2)
DECLARE @RP_enhance_ESOL decimal (18,2)
DECLARE @RP_total decimal (18,2)
DECLARE @LP_basic decimal (18,2)
DECLARE @subj_area_weight decimal (18,2)
DECLARE @LP_weighted decimal (18,2)
DECLARE @LP_enhance_SLDD decimal (18,2)
DECLARE @LP_enhance_VOC decimal (18,2)
DECLARE @LP_enhance_AGE decimal (18,2)
DECLARE @LP_enhance_TSN decimal (18,2)
DECLARE @LP_enhance_HE decimal (18,2)
DECLARE @LP_enhance_FEE decimal (18,2)
DECLARE @LP_total decimal (18,2)
DECLARE @OP decimal (18,2)
DECLARE @spurs_total decimal (18,2)
DECLARE @student_SPURS_total decimal(18,2)
DECLARE @out_weight decimal (18,2)
DECLARE @main_course_study tinyint
DECLARE @exp_days int
DECLARE @act_days int
DECLARE @total_students bigint
DECLARE @total_enrolments bigint
DECLARE @acad_year_spurs decimal (18,2)
DECLARE @RP_1960 tinyint
DECLARE @RP_null_DOB tinyint
DECLARE @RP_TSN tinyint
DECLARE @RP_disab2 tinyint
DECLARE @RP_disab3 tinyint
DECLARE @RP_disab4 tinyint
DECLARE @RP_ESOL tinyint
DECLARE @LP_1960 tinyint
DECLARE @LP_null_DOB tinyint
DECLARE @LP_TSN tinyint
DECLARE @LP_disab2 tinyint
DECLARE @LP_disab3 tinyint
DECLARE @LP_disab4 tinyint
DECLARE @LP_infill tinyint
DECLARE @LP_infill_1960 tinyint
DECLARE @LP_infill_null_DOB tinyint
DECLARE @LP_infill_TSN tinyint
DECLARE @LP_infill_disab2 tinyint
DECLARE @LP_infill_disab3 tinyint
DECLARE @LP_infill_disab4 tinyint
DECLARE @LP_term2 tinyint
DECLARE @LP_term2_1960 tinyint
DECLARE @LP_term2_TSN tinyint
DECLARE @LP_term2_disab2 tinyint
DECLARE @LP_term2_disab3 tinyint
DECLARE @LP_term2_disab4 tinyint
DECLARE @LP_term3 tinyint
DECLARE @LP_term3_1960 tinyint
DECLARE @LP_term3_TSN tinyint
DECLARE @LP_term3_disab2 tinyint
DECLARE @LP_term3_disab3 tinyint
DECLARE @ndaq_qual nvarchar (12)
DECLARE @base CURSOR
UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period
DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)
SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00
SET @base = CURSOR LOCAL FAST_FORWARD
FOR
SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription,
stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total,
stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0)
AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code,
stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date,
stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/
delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00)
AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref
FROM stsacper INNER JOIN
stmaos INNER JOIN
stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND
stmaos.aos_period = stcsessd.aos_period INNER JOIN
stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND
stmaos.aos_period = stcfesdt.aos_period INNER JOIN
stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND
stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN
stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND
stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN
delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN
stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN
deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN
delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN
delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN
stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN
delsubjarea RIGHT OUTER JOIN
delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join
stcstdet on stmaos.aos_code = stcstdet.aos_code inner join
nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref
where stmaos.acad_period = @p_acad_period
and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B')
AND (stmaos.stage_ind = 'E')
AND (stcfesdt.qual_aim IS not null)
AND (stcfesdt.qual_aim IS not null)
and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL)
and stmaos.start_date is not null
and stmaos.start_date <= getdate()
OPEN @base
FETCH NEXT FROM @base
INTO @student_id, @acad_period, @aos_code, @aos_period, @full_desc_static, @full_desc_session,
@dept_code, @aos_start_dt, @exp_length, @unit_length, @student_year,
@hrs_per_week, @aos_end_dt, @no_of_weeks, @hrs_total,@student_type,
@moa_code, @qual_aim, @subject_code,@subject_area_group, @course_group,
@nvq_lvl_ind, @geolocn_code, @surname, @forename, @birth_dt, @post_code,
@student_attend_mode, @fee_waiver, @start_date, @student_status, @end_date,
@spec_learn_disab, @age, @ESOL, @threshold1, @threshold2, @threshold3, @outcome, @basicIT,
@subj_area_weight, @out_weight, @additionality_yn,@ndaq_qual
when i run the follwoing i get
exec testflu '06/07'
Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
i am unsure cos from what i can see i ahve included all, am i missing something
View 7 Replies
View Related
May 30, 2008
Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
View 2 Replies
View Related
Nov 15, 2005
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
View 5 Replies
View Related
Jul 11, 2006
All, is there a way of getting all table names that contain a columnname?I'm looking at a DB that has 125+ tables and I'm interested in findingall table names that contain the column order_date.How can I do it?TIA,Sashi
View 2 Replies
View Related
Aug 23, 2007
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml
The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance!
Here's the sproc:
ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
View 8 Replies
View Related
Sep 29, 2005
Hi!
I need a stored procedure with this basic setup:
CREATE PROCEDURE test
@Type int
AS
SELECT *
FROM
Cards
WHERE
CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
How can this be done?
Thanks!
/Rickard
View 2 Replies
View Related
Feb 9, 2006
Hi,
I want to convert a SQL query as shown below into a stored procedure:
select name
from namelist
where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
Thanks.
View 5 Replies
View Related
Mar 21, 2008
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
View 5 Replies
View Related
Nov 21, 2006
I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.
Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.
Public Sub Main()
Dim vars As Variables
Dim DropVariable As String
Dim CreateVariable As String
Dim InsertVariable As String
DropVariable = "Execute dbo.[sp_DropTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"
CreateVariable = "Execute dbo.[sp_CustomTables] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"
InsertVariable = "Execute dbo.[sp_InsertTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"
Dts.VariableDispenser.LockOneForWrite("varDropTable", vars)
Dts.VariableDispenser.LockOneForWrite("varCreateTable", vars)
Dts.VariableDispenser.LockOneForWrite("varInsertTable", vars)
Dts.Variables("varDropTable").Value = DropVariable
Dts.Variables("varCreateTable").Value = CreateVariable
Dts.Variables("varInsertTable").Value = InsertVariable
'MsgBox(Dts.Variables("varDropTable").Value)
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
View 4 Replies
View Related
Mar 2, 2012
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
View 1 Replies
View Related
