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:--EmployeeDeclare @FName Varchar(25);Declare @LName Varchar(25);Declare @Address Varchar(100);Declare @LookupCode varchar(25);Declare @LookUpCodeid int;Declare @EmployeeEntitty int;Declare @UniqContactId int; --AgencyDeclare @AgencyCode Varchar(25);Declare @AgencyName Varchar(25);Declare @AgencyID as int; --Change Variables here--Employee Variables Startset @FName = 'Rob'Set @LName = 'Smith'set @Address ='55 Fake St'--Employee Variables End--Agency Variables Start--Agency Variables EndSet @AgencyCode = 'CI2'Set @AgencyCode = 'Charlie Insurance'-- Find LookupcodeSet @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)beginset @LookUpCodeid = @LookUpCodeid + 1Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))end--Create EmployeeINSERT INTO entity (entkey, lookupcode, nameof) VALUES ('EMPL', @LookupCode, @FName + ' ' + @LName)set @EmployeeEntitty = @@identityINSERT 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 qINSERT 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 AgencyINSERT 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 = @@identityassp_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