|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
IF Statement - Whats NextUSE DataInput GO CREATE PROC a_spAppendtoManualContribution @CompanyId int IF (SELECT COUNT(*) AS 'Number of Rows' FROM ManualContribution WHERE CompanyId = @Company)>0 /* Insert Records into the Manual Contribution Table */ INSERT INTO ManualContribution (CompanyId,EmployeeUID,FullName,Post) SELECT Employees.CompanyId,Employees.EmployeeUID,Employees.Last +', '+ Employees.First +' '+ ISNULL( Employees.Middle,'' ) AS 'FullName','0' FROM Contributions, Employees WHERE CompanyId = @CompanyId AND Employees.AllowContributions = 1 ORDER BY FullName Thanks Steve You are missing the keyword [AS]
CREATE PROCEDURE dbo.MyProcedure ( @Parameter datatype ) AS IF ... -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Stephen Lynch" <raider1rai***@yahoo.com> wrote in message news:44f884cb$0$3571$815e3792@news.qwest.net... > Can I use an If statement on a StProc? I am erroring around the IF. > > USE DataInput > > GO > > CREATE PROC a_spAppendtoManualContribution > > @CompanyId int > > IF > > (SELECT COUNT(*) AS 'Number of Rows' > > FROM ManualContribution > > WHERE > > CompanyId = @Company)>0 > > /* Insert Records into the Manual Contribution Table */ > > INSERT INTO ManualContribution > > (CompanyId,EmployeeUID,FullName,Post) > > SELECT Employees.CompanyId,Employees.EmployeeUID,Employees.Last +', '+ > Employees.First +' '+ ISNULL( Employees.Middle,'' ) AS 'FullName','0' > > FROM Contributions, Employees > > WHERE CompanyId = @CompanyId AND > > Employees.AllowContributions = 1 > > ORDER BY FullName > > > > Thanks > > > > Steve > > You are missing AS after the parameter list. Also the ORDER BY in the SELECT statement for the
INSERT is meaningless. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Stephen Lynch" <raider1rai***@yahoo.com> wrote in message news:44f884cb$0$3571$815e3792@news.qwest.net... > Can I use an If statement on a StProc? I am erroring around the IF. > > USE DataInput > > GO > > CREATE PROC a_spAppendtoManualContribution > > @CompanyId int > > IF > > (SELECT COUNT(*) AS 'Number of Rows' > > FROM ManualContribution > > WHERE > > CompanyId = @Company)>0 > > /* Insert Records into the Manual Contribution Table */ > > INSERT INTO ManualContribution > > (CompanyId,EmployeeUID,FullName,Post) > > SELECT Employees.CompanyId,Employees.EmployeeUID,Employees.Last +', '+ Employees.First +' '+ > ISNULL( Employees.Middle,'' ) AS 'FullName','0' > > FROM Contributions, Employees > > WHERE CompanyId = @CompanyId AND > > Employees.AllowContributions = 1 > > ORDER BY FullName > > > > Thanks > > > > Steve > > Steven,
One other comment about your [IF] statement. Using the various forms of count() requires the Query Processor to scan all of the data (that meets the filter criteria). If you were to use [IF EXISTS] instead, the QP will stop looking as soon as it determines that there is just one row that meet the filter criteria. So, IF EXISTS ( SELECT ... ) {do something} may be more efficient than, IF (SELECT count(*)...) > 0 {do something} Especially when handling large tables, or large numbers or rows meeting the filter criteria. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Stephen Lynch" <raider1rai***@yahoo.com> wrote in message news:44f884cb$0$3571$815e3792@news.qwest.net... > Can I use an If statement on a StProc? I am erroring around the IF. > > USE DataInput > > GO > > CREATE PROC a_spAppendtoManualContribution > > @CompanyId int > > IF > > (SELECT COUNT(*) AS 'Number of Rows' > > FROM ManualContribution > > WHERE > > CompanyId = @Company)>0 > > /* Insert Records into the Manual Contribution Table */ > > INSERT INTO ManualContribution > > (CompanyId,EmployeeUID,FullName,Post) > > SELECT Employees.CompanyId,Employees.EmployeeUID,Employees.Last +', '+ > Employees.First +' '+ ISNULL( Employees.Middle,'' ) AS 'FullName','0' > > FROM Contributions, Employees > > WHERE CompanyId = @CompanyId AND > > Employees.AllowContributions = 1 > > ORDER BY FullName > > > > Thanks > > > > Steve > > Thanks,
I'll try it "Arnie Rowland" <ar***@1568.com> wrote in message news:uQIEktfzGHA.3512@TK2MSFTNGP04.phx.gbl... One other comment about your [IF] statement. Using the various forms of count() requires the Query Processor to scan all of the data (that meets the filter criteria). If you were to use [IF EXISTS] instead, the QP will stop looking as soon as it determines that there is just one row that meet the filter criteria.Steven, So, IF EXISTS ( SELECT ... ) {do something} may be more efficient than, IF (SELECT count(*)...) > 0 {do something} Especially when handling large tables, or large numbers or rows meeting the filter criteria. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "Stephen Lynch" <raider1rai***@yahoo.com> wrote in message news:44f884cb$0$3571$815e3792@news.qwest.net... > Can I use an If statement on a StProc? I am erroring around the IF. > > USE DataInput > > GO > > CREATE PROC a_spAppendtoManualContribution > > @CompanyId int > > IF > > (SELECT COUNT(*) AS 'Number of Rows' > > FROM ManualContribution > > WHERE > > CompanyId = @Company)>0 > > /* Insert Records into the Manual Contribution Table */ > > INSERT INTO ManualContribution > > (CompanyId,EmployeeUID,FullName,Post) > > SELECT Employees.CompanyId,Employees.EmployeeUID,Employees.Last +', '+ > Employees.First +' '+ ISNULL( Employees.Middle,'' ) AS 'FullName','0' > > FROM Contributions, Employees > > WHERE CompanyId = @CompanyId AND > > Employees.AllowContributions = 1 > > ORDER BY FullName > > > > Thanks > > > > Steve > >  |
|||||||||||||||||||||||
Other interesting topics