SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to run stored procedure in query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brian_Charlie
Starting Member

2 Posts

Posted - 09/24/2012 :  11:48:02  Show Profile  Reply with Quote
Currently I am working in SQL Query to make a script for my company which allows us to set up certain things for our program. Building this script to save time, because everytime we set up a new Virtual Machine we have to enter this data. So we will adventually execute this as it will populate the tables for us.

Anyways my issue is that we have a stored procedure built and I would like to pass it the data but I am recieving the error of "Incorrect Syntax near 'assp_insert_agency_subaccounts'" now I know I am passing the correct # of data, I've opened a new query window and placed the 4 variables, and it worked fine.. I am not sure why its not working in this sql query I have created..
The line that is wrong is found towards the bottom before the last insert statement. Here is what I have wrote so far:
--Employee
Declare @FName Varchar(25);
Declare @LName Varchar(25);
Declare @Address Varchar(100);
Declare @LookupCode varchar(25);
Declare @LookUpCodeid int;
Declare @EmployeeEntitty int;
Declare @UniqContactId int; 
--Agency
Declare @AgencyCode Varchar(25);
Declare @AgencyName Varchar(25);
Declare @AgencyID as int; 


--Change Variables here
--Employee Variables Start
set @FName = 'Rob'
Set @LName = 'Smith'
set @Address ='55 Fake St'
--Employee Variables End
--Agency Variables Start

--Agency Variables End
Set @AgencyCode = 'CI2'
Set @AgencyCode = 'Charlie Insurance'

-- Find Lookupcode
Set @LookUpCodeid = 1 
Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))
While Exists(Select LookupCode  From Entity Where LookupCode = @LookUpCode)
begin
set @LookUpCodeid = @LookUpCodeid + 1
Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))
end

--Create Employee
INSERT INTO entity (entkey, lookupcode, nameof) VALUES ('EMPL', @LookupCode, @FName + ' ' + @LName)

set @EmployeeEntitty = @@identity

INSERT INTO dbo.contactname (uniqentity, uniqcontactaddressmain, lkprefix, firstname, middlename, lastname, lksuffix, categorycode, 
descriptionof, comments, insertedbycode, flags, lklanguage) 
Values (@EmployeeEntitty, -1, '', @FName , '', @LName, '', 'C', '', '', 'ENTERPRISEADMIN', 64, '')

Set @UniqContactId = (Select uniqcontactname From dbo.ContactName Where uniqentity = @EmployeeEntitty )

INSERT INTO employee (uniqentity, lookupcode, nameof, uniqcontactnameaccount, uniqcontactaddressaccount, uniqcontactnumberresidenceaccount, uniqcontactnumbermobileaccount, batchpaymentmethodcode, uniqcontactnumberfaxaccount, uniqcontactnumberemailaccount, uniqglaccountpay, uniqglaccountdef, uniqglaccountexp, uniqglaccountabill, uniqglaccountdbill, insertedbycode, roleflags, flags, uniqglaccountpaywriteoff) Values (@EmployeeEntitty, @LookupCode, @FName + ' ' + @LName, @UniqContactId , -1, -1, -1, '', -1, -1, -1, -1, -1, -1, -1, 'ENTERPRISEADMIN', 0 & 2145386497, 0 | 154 & 2147479707, -1)

INSERT quicklinkemployeenodeorder SELECT @EmployeeEntitty, q.uniqquicklinknode, (SELECT COUNT(*) FROM quicklinknode qn WHERE qn.nodenumber <= q.nodenumber) FROM quicklinknode q

INSERT quicklinkemployeeservicingrole SELECT @EmployeeEntitty, uniqcdservicingrole FROM cdservicingrole WHERE uniqcdservicingrole > 0 

INSERT INTO distributionemailaccount(uniqentity, uniqagency, displayname, replyaddress, insertedbycode) SELECT @EmployeeEntitty, sc.uniqagency, e.nameof, cn.emailweb, 'ENTERPRISEADMIN' FROM employee e CROSS JOIN agency a CROSS JOIN contactnumber cn CROSS JOIN structurecombination sc WHERE a.uniqagency = sc.uniqagency AND e.uniqcontactnumberemailaccount = cn.uniqcontactnumber AND e.uniqentity = @EmployeeEntitty AND e.flags & 128 = 128 GROUP BY e.uniqentity, sc.uniqagency, e.nameof, cn.emailweb 

UPDATE dea SET dea.flags = dea.flags | 2 FROM distributionemailaccount dea INNER JOIN(SELECT d.uniqentity, d.uniqagency 					 FROM distributionemailaccount d            WHERE d.uniqentity = @EmployeeEntitty 					 AND d.uniqagency = (SELECT MIN(d2.uniqagency) 															 FROM distributionemailaccount d2 															 WHERE d.uniqentity = d2.uniqentity) 					 GROUP BY d.uniqentity, d.uniqagency) AS dt ON dea.uniqentity = dt.uniqentity AND dea.uniqagency = dt.uniqagency WHERE dea.uniqentity = @EmployeeEntitty

--Create Agency
INSERT INTO agency (agencycode, nameof, licensenumber, uniqregion, initialmonth, firstfiscalmonth, address1main, address2main, address3main, citymain, cdstatecodemain, postalcodemain, countymain, cdcountrycodemain, address1mailing, address2mailing, address3mailing, citymailing, cdstatecodemailing, postalcodemailing, countymailing, cdcountrycodemailing, phonenumber, phoneextension, phonedescription, faxnumber, faxextension, faxdescription, email, website, fein, insertedbycode, phonecountrycode, faxphonecountrycode, regionprovincemain, regionprovincemailing) Values (@AgencyCode, @AgencyName , '', -1, year(getdate())+ Month(getdate()), '03', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'ENTERPRISEADMIN', '', '', '', '')

set @AgencyID = @@identity

assp_insert_agency_subaccounts  @agencyid, @AgencyCode, @AgencyName, 'ENTERPRISEADMIN'

INSERT INTO DMSEmail (uniqagency, accountname, displayname, replyaddress, emailaddress, username, pswd, primaryserveraddress, primaryportnumber, backupserveraddress, backupportnumber, customdisclaimer, flags, insertedbycode, inserteddate, updatedbycode, updateddate, attachastype) SELECT @agencyid, @AgencyCode, '', '', '', '', '', '', 25, '', 25, '', 0, 'ENTERPRISEADMIN', current_timestamp, '', NULL, 0 FROM DMSEmail WHERE UniqAgency = @agencyid HAVING COUNT(UniqAgency) = 0


Thanks in advance

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  12:00:18  Show Profile  Reply with Quote
When you want to execute a stored procedure you must prefix it with the EXEC keyword unless it is the first statement in a batch. So just add an EXEC before the stored proc name.

....
set @AgencyID = @@identity

EXEC assp_insert_agency_subaccounts  @agencyid, @AgencyCode, @AgencyName, 'ENTERPRISEADMIN'
....

As an aside, and unrelated to your question: depending on your requirements, you may want to use @@identity or scope_identity in the statement above that: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
Go to Top of Page

Brian_Charlie
Starting Member

2 Posts

Posted - 09/24/2012 :  15:10:24  Show Profile  Reply with Quote
Thanks, works perfect, I totally forgot about Exec. I will also look into your web link
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000