7 Replies - 1250 Views - Last Post: 18 January 2011 - 08:53 AM Rate Topic: -----

#1 edwin g  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-August 10

stored procedure

Posted 17 January 2011 - 03:02 PM

I need some help with some stored procedure.I just created a database and some stored procedure in SQL Server Management Studio and i don't know where I should save to see them in Visual Studio(in Server Explorer window). PS : i use linq and c#(not visual basic). I'll be very grateful if someone can help me with this problem. Thank you for your time.
Is This A Good Question/Topic? 0
  • +

Replies To: stored procedure

#2 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: stored procedure

Posted 17 January 2011 - 03:18 PM

Wouldn't the stored proc belong in the database? And what do you mean save them? Wouldn't you just set up the connection info in Visual Studio to see the database?
Was This Post Helpful? 0
  • +
  • -

#3 edwin g  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-August 10

Re: stored procedure

Posted 17 January 2011 - 03:38 PM

View PostForcedSterilizationsForAll, on 18 January 2011 - 12:18 AM, said:

Wouldn't the stored proc belong in the database? And what do you mean save them? Wouldn't you just set up the connection info in Visual Studio to see the database?


I created them by expanding "programmability" tree of my database then i clicked(right click) on "Stored Procedure" -> "New Stored Procedure". Then in Visual Studio I created a .dbml file and i atached all my tables (of course after i connected to my DataBase).
Like i said above my problem is that a can't see my procedure when i expand "Stored Procedure" from Server Explorer window in Visual Studio. I'm new with this stuff and i'm sorry if i didn't explain to well.
Was This Post Helpful? 0
  • +
  • -

#4 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: stored procedure

Posted 17 January 2011 - 03:48 PM

Are you able to call the stored proc by name in the code, regardless of it showing in Visual Studio? It's been a while since I've done any database calling in .net so I can't give you an example off the top of my head, but you should be able to specify the source of the datastore with the name of the stored proc.
Was This Post Helpful? 0
  • +
  • -

#5 edwin g  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-August 10

Re: stored procedure

Posted 17 January 2011 - 04:21 PM

View PostForcedSterilizationsForAll, on 18 January 2011 - 12:48 AM, said:

Are you able to call the stored proc by name in the code, regardless of it showing in Visual Studio? It's been a while since I've done any database calling in .net so I can't give you an example off the top of my head, but you should be able to specify the source of the datastore with the name of the stored proc.


I'm not able to do that that's why I thought i have a problem with the visual studio or i didn't create the procedures in the right way.
Was This Post Helpful? 0
  • +
  • -

#6 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: stored procedure

Posted 17 January 2011 - 09:57 PM

Is your procedure showing in SQL Server Management Studio? If you go under Programmability>Stored Procedures for the database it should show if you've done it properly.

Then you can try setting up the connection string and running a generic select query to see if it returns any data. Add a Try/Catch/Finally to see if you're getting any errors and what they are. You'll probably need to show your code if you want more help.
Was This Post Helpful? 0
  • +
  • -

#7 edwin g  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-August 10

Re: stored procedure

Posted 18 January 2011 - 01:49 AM

View PostForcedSterilizationsForAll, on 18 January 2011 - 06:57 AM, said:

Is your procedure showing in SQL Server Management Studio? If you go under Programmability>Stored Procedures for the database it should show if you've done it properly.

Then you can try setting up the connection string and running a generic select query to see if it returns any data. Add a Try/Catch/Finally to see if you're getting any errors and what they are. You'll probably need to show your code if you want more help.


i just found on msdn how to create properly a procedure. I didn't know you should click "Execute" after you finish each procedure.Thank you very much for your time and for your help.
Was This Post Helpful? 0
  • +
  • -

#8 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: stored procedure

Posted 18 January 2011 - 08:53 AM

Are you doing it as something like:

DECLARE @sql VARCHAR(2000)

SET @sql = 'SELECT * FROM Table1 WHERE column = ' + @parameter1

EXEC(@sql)



where @parameter is a parameter?

If you must dynamically build your sql statement in the stored proc, there is a better way to do it than just EXECUTE. I forget exactly what it is but it's one that you include all the parameters. This will allow SQL to optimize it and keep it in the cache instead of having to rebuild each time a different parameter is used.

Instead you want to do something like the following (from http://blog.sqlautho...best-practices/):

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25


This post has been edited by ForcedSterilizationsForAll: 18 January 2011 - 08:55 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1