Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to run stored procedure in query

Author  Topic 

Brian_Charlie
Starting Member

2 Posts

Posted - 2012-09-24 : 11:48:02
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 12:00:18
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 - 2012-09-24 : 15:10:24
Thanks, works perfect, I totally forgot about Exec. I will also look into your web link
Go to Top of Page
   

- Advertisement -