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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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:
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; 
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)
set @LookUpCodeid = @LookUpCodeid + 1
Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))

--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

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:
Go to Top of Page

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  
 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.05 seconds. Powered By: Snitz Forums 2000