simple question on stored procedures 2

[Spyrios]

Ok,

so I know how to write a stored procedure, but after I write a procedure that does whatever I want it to do, how do I make it happen? I'm getting more familiar with SQL Server, at least this part and that part and this other part, but I'm missing the big picture. I'm coming from Access, where if I want lets say a report displayed, the report is based off a query which sends the info over. But as I understand it, views don't accept stored procedures. How are stored procedures generally used? I'm assuming that to use a sp you don't go into the Query Analyzer every time you want to run it. The only idea that I've had is that you use DTS in some way to pull the info from a sp at a given time and send it over to a web page, excel file, access file, etc. Sorry for the simplistic nature of my question, but I need to find some way to tie the pieces of SQL Server together. I know there could probably be over a million different responses depending on what I want to actually do, but I'm just looking for a general picture.

Thanks

 

[SQLSister]

YOur front end calls the stored procedure. For how exactly to do that, you need to go to the information from your front end. Access vba help should tell you how to call a stored procedure if Access is your front end.

 

[philhege]

Generally, you save the stored procedure in a database. Then you call it with the EXEC command from any number of software packages that can connect to SQL Server.

EXEC mysp_GetTodayOrders '03/04/2004'

is what you would put in the Query Analyzer.

Cold Fusion:
<cfquery datasource="MySQLServer" name="getorders">
EXEC mysp_GetTodayOrders '03/04/2004'
</cfquery>

VBScript:
..(other stuff to open the connection, blahblahblah)
SQLCtrlString = "EXEC mysp_GetTodayOrders '03/04/2004'"
'run the query
set rs1 = CN.Execute(SQLCtrlString)

and so on. You can call stored procedures from Crystal Reports, C#, .NET, on and on. SPs have the advantage of having a cached plan, so they're quicker than on-the-fly queries. You can control access to your data with SPs, restricted operations to SP execute only in some arenas.

I'm sure others will weigh in. Once you get the hang of these, you won't go back to ad hoc query coding, believe me.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[Spyrios]

thank you both for your answers, that was exactly what I needed.

 

[bartsimpson]

I'm in a similar position to Spyrios, but my front end is fixed - I need to use Excel. I have created a stored procedure, which works using Query Analyser, but I can't see it in the ODBC connector from Excel.

Where do I go next ?

 

[SQLBill]

How do you login when using QA?
How do you login when using Excel?

You might need to give your Excel login access to the stored procedure. In Enterprise Manager, drill down to the database, expand that, click on Users, double click on the user's name. Click Permissions. Find the stored procedure and make sure the user has access to execute it.

-SQLBill

 

[bartsimpson]

I'm using the same login parameters for both Excel & QA - Windows authentication. I can see views & display them OK in Excel, but not SPs. I have granted EXEC rights to the group that I belong to in Enterprise Manager.

 

[philhege]

You have to use VBA. Here's one example:

http://groups.google.com/groups?hl=...el+sql+server+stored+procedure&ie=UTF-8&hl=en

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[bartsimpson]

philhege,

That's exactly what I needed. Thank you.
I've been able to change the code to put the data exactly where I need it.
I also had to make sure that I had enabled the ActiveX Data Objects library by doing the following :

Choose Tools/References from the Visual Basic Editor menu and put a
check mark beside the Microsoft ActiveX Data Objects 2.X Library, where X is
the highest number you see in the list

Thanks to Rob Bovey at Excel Help Forum for this :

http://www.excelforum.com/showthread.php?t=265289

 

[bartsimpson]

I'm still having problems, however ...

I'm passing two parameters to the stored procedure - a number that corresponds to the month, and a number that corresponds to the year. This is then used in the SP to find a date, :

CONVERT(DATETIME, '@year-@month-06 10:22:00', 102)

I can get the code to work well if I hard code numbers into the SP, but I get an error if I use the variables passed from Excel to the SP -

ODBC SQL Server error, Syntax error converting DATETIME from character string.

I've made sure that the cells that pass the parameters to the SP are number types, and have declared the variables in the SP as integers. Any ideas ?

 

[philhege]

The datetime parser expects a string, e.g. "2004-10-06 12:00:00". So,

CONVERT(DATETIME, @year+'-'+@month+'-06 10:22:00', 102)

SQL gurus suggest that you use CAST instead of CONVERT:

CAST(@year+'-'+@month+'-06 10:22:00' AS DATETIME)

HTH,




Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[bartsimpson]

Phil,

I see. This is making sense to me. However, I have tried both methods, and I get the same error with either :

"Syntax error converting the VARCHAR value '06 10:22:00' to a column of data type INT

The number in Excel is an integer, and the SP makes sure that the parameters passed to it are integers too.

 

[philhege]

If you're passing the values in as INT, you'll have to CAST them as VARCHAR somewhere in the SP before you can use them in the function.

The reason you're getting the error that you're getting is because the concatenation logic sees the integer first, and rules that the rest of the concatenation must match the data type of the first item.

HTH,





Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[bartsimpson]

That's it ! I had tried various methods, but the little bit of debugging code that I had left in was looking for an INT when I had previously tried to change the data type. This was then giving me an error. Doh !

The error handling in a SP isn't particularly good....

Thanks again.