Calling Stored Procedure issue in Excel 2013
-
Hi All,
I'm very new to SQL please bear with me..
I have a stored procedure that does what I expect it to do in SQL returning the data for any year period I enter, but I am having difficulty binding the stored procedure to an Excel Spreadsheet which won't allow me to do so due to an error it's returning when trying to convert varchar to int. It's a relatively small piece of SQL but essentially I want to bind the @Year parameter to an Excel cell for an end user.
It states "Error converting data type varchar to tinyint" in Excel. Any help is much welcomed. Below is the stored procedure.
My definition string in Excel:
EXECUTE dbo.StoredProcedure 'Year'
The stored procedure:
@Year SmallInt
AS
SELECT DBO.users.loginname,
Cast(Datename(M, DBO.contractdate.contractdate) AS VARCHAR) AS DatePeriod,
Sum(CASE
WHEN DBO.contract.contract = 1 THEN 1
ELSE 0
END) AS Contract,
Sum(CASE
WHEN DBO.contract.contract = 0 THEN 1
ELSE 0
END) AS Permanant
FROM DBO.contractskill
INNER JOIN DBO.users
INNER JOIN DBO.contract
ON DBO.users.userid = DBO.contract.userid
INNER JOIN DBO.contractdate
ON DBO.contract.contractid = DBO.contractdate.contractid
ON DBO.contractskill.contractid = DBO.contract.contractid
WHERE ( DBO.contractdate.dns = 0 )
AND ( DBO.contractskill.skillid = 30960 )
AND Year(DBO.contractdate.contractdate) = @Year
GROUP BY Cast(Datename(M, DBO.contractdate.contractdate) AS VARCHAR),
Month(DBO.contractdate.contractdate),
DBO.users.loginname
-
I've found this technique to work in Excel 2013:
http://codebyjoshua.blogspot.com/2012/01/get-data-from-sql-server-stored.html
for the command text use the format:
{CALL dbname.dbo.GetData (?)}in the Parameters dialog, there's an option to "Get the value from the following cell"
where you can select the cell in your sheet that the user enters the value. You can also if you want click the checkbox at the bottom of the Parameters dialog that says "Refresh automatically when cell value changes"
-
Thanks for the response Chris unfortunately this did not work for me, I've included the screenshot of the error and my SQL statement in MS query.
-
In your CALL query, you need to put an actual ? instead of the word Year so that Excel Query knows it's a parameter. The first time you do this, you'll get a popup dialog asking for a sample value. Afterwards you can go back into the connection properties and setup the parameter to point to a cell in the worksheet.
-
Hi Chris,
I have tried this but encounter the same issue?
-
it looks like this time you're missing the database name, you started with dbo. instead of fgsvrtrisys.dbo.
when you got the popup asking "Enter Parameter Value" what did you type in? it should be a sample value of the same datatype that is the parameter of your stored proc
-
I have entered both the full string fgsvrtrisys.dbo.SPname and just dbo.SPName neither have worked and result in the same error in the screenshot, the parameter I am entering in the prompt is just 'Year' without the 's which is passed in the stored procedure as @Year declared as a smallint in SQL.
-
Microsoft Query has its quirks. Try hard-coding the year value at first, for example: {Call YourProcedureName (2016)}. Don't enclose 2016 in single quotes. Return result to Excel worksheet.
Then while in worksheet click on Data menu, choose Connections, select the connection and click Properties, then select Definition tab. You will see your sql statement in the Command Text box. Now change the year value to ? i.e. (2016) to (?), as Chris has advised. The parameter box will now pop-up when you refresh; enter a year and the worksheet range will be refreshed. You can also click the now active Parameters button on the Definitions tab and link the parameter value to a cell in the workbook.
Two other observations: The YEAR function returns an int datatype, so @Year should be int; why apply CAST to DATENAME function as it returns a nvarchar? I hope this solves your problem. I'm assuming you are using Excel 2013. The Definitions tab in its present form is from Excel 2007 onward.
-
Another observation: The join between the contractskill and users tables is missing from the stored procedure code posted -
"FROM DBO.contractskill
INNER JOIN DBO.users
INNER JOIN DBO.contract
ON DBO.users.userid = DBO.contract.userid"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply