| Author |
Topic |
|
majkinetor
Yak Posting Veteran
55 Posts |
 Posted - 2006-03-23 : 06:06:29
|
I have table variable in my store procedure.I want to send it to function.AFAIK, table variables are not supported as formal parameters, alternatively I can send string end execute dinamic statement but I don't see how can I access variable from calling store procedure this way. I could create temporary table instead of variable but I want to consult you first about this. So, what I need is this (pseudocode):PROCEDURE storeproc (@Count INT OUTPUT)BEGIN DECLARE @xTab TABLE(Element nvarchar(50), Value nvarchar(256)) INSERT INTO @xTAb ..... @Count = fHandleTable(@tTab)END-----------------FUNCTION fHandleTable (@tab TABLE) RETURN INTBEGIN RETURN (select count(*) from @tab)END I don't acctualy need to return count, this is just an example.Thx.www.r-moth.com |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-03-23 : 06:15:28
|
| if you can tell use what actually you want to do in the function fhandletable then we will be able to give you better idea.. Sucess Comes to those who Believe in Beauty of their Dream.. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-23 : 06:38:51
|
| You cant use Dynamic SQL inside function. You need to make use of Sp with dynamic sqlMadhivananFailing to plan is Planning to fail |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-23 : 07:02:34
|
2 chiragkhabariaWhy do you need that info ? I don't need better idea, I need information is this possible or no OR what alternatives I have to above code. Consider this as theorethical question. 2 madhivanan I didn't know that. It seems that I can't never get used to many nonsences of Microsoft products.I guess I will have to use temporary table. I found this on local space:quote:
If stored procedure A creates a temp table and calls stored procedure B, then B will be able to use the temp table that A created
I guess the same is true with functions, so I can use temp table instead table variable.www.r-moth.com |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-03-23 : 07:31:27
|
quote:
If stored procedure A creates a temp table and calls stored procedure B, then B will be able to use the temp table that A created
Yeah you can use the temp tables only if they are global temp tables.. Before using the temp tables just read scope of Different type of Temp tables in SQL Book online so that you can get the better idea about it. Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-23 : 07:56:47
|
>>It seems that I can't never get used to many nonsences of Microsoft products.It seems you dont know how to use them  MadhivananFailing to plan is Planning to fail |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-23 : 07:59:49
|
Global Temp tables don't drink watter for me. And you are obviously wrong, they need not to be global.I changed my code like this:PROCEDURE storeproc (@ErrorCode INT OUTPUT)BEGIN -- DECLARE @xTab TABLE(Element nvarchar(50), Value nvarchar(256)) SELECT ... INTO #tTAb ..... EXEC pHandleTable @ErrorCodeEND-----------------PROCEDURE pHandleTable (@pErrorCode INT OUTPUT)BEGIN select ... from #tTab set pErrorCode = ...END The only problem I have with this scenario is that somehow pErrorCode isn't changed ? Does anybody know why ?www.r-moth.com |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-03-23 : 08:11:52
|
You need to specify an output parameter when you call it as well... e.g.EXEC pHandleTable @ErrorCode OUTPUT Ryan Randallwww.monsoonmalabar.comIdeas are easy. Choosing between them is the hard part. |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-23 : 08:17:02
|
| RyanRandall!thx manwww.r-moth.com |
|
|
|
jhermiz
3564 Posts |
Posted - 2006-03-24 : 09:52:39
|
majkinetor,This comment is not for just this post but other posts. In some of your posts you make comments like this is NOT what i want to do I just want a yes or no answer. To me thats rude, people are giving you options for a reason, the reason being is your current solution sucks a** (excuse the language). So if you have an issue than expect feedback, corrections, or consider other methods given to you. They are given to you for a reason. We understand you want answers, but this is free advice so take it or leave it.Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-28 : 04:16:03
|
| 2jhermiz>> To me thats rudeTo you, there might be elephants on Mars.... Me, I don't have problem even with that, that is your worldTo me, the rude part comes in: "your current solution sucks a** (excuse the language)". You will never hear me using that kind of language, and if we think about that a little more, it is far from rude and closer to depreciation which is much worst in therms of human behavior.>> They are given to you for a reason I don't doubt most of the people here post for a reason. But I also understand due to my active nightlife on some other forums that there are other kind of people who have other reasons to post but to help. Those reasons may be various, and most of them are covered in basic sociology/psychology course. >>So if you have an issue than expect feedback, corrections, or consider other methods given to youPeople have their own means of solving issues, so I have mine, and like I see, you have yours. If you don't like my behavior or if you find me rude, impolite or anything, U can skip my posts, and not answer my questions. It is that easy. On some forums we have even IGNORE button, so some members of comunity can IGNORE people they choose. Maybe we should have one here...But, vainity does its dirty job here, and if you were really calm guy and if your hobby was really "reading a bible", you would act in different direction (not oposite, just different).www.r-moth.com |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-28 : 07:49:53
|
| majkinetorWhat is wrong with someone asking a question to see if they can offer you an alternative solution? Your questions are not always very clear, and your English and spelling are not that easy to understand, so it is natural for people to ask follow-up questions to make sure they understand what you want. You seem to go out of your way to be rude to people when they ask a question. If you don't feel like responding to a question, just follow your own advice, and ignore it. There are not that many people on this forum that post responses, and once you offend all of them, you won’t get any more help.CODO ERGO SUM |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-28 : 08:08:27
|
| Michaelhmm.... nobody had a problem with my english before. I know it is not by the book, but it is far from what you described as "not easy to understand". Spelling errors are different thing, they are normal - everybody makes them - and I am trying to correct everyone I notice.About your other note:If they don't want to answer me, so be it - this is not the only place for SQL in the world. I am not rude - people maybe don't like my answers but that, in fundament, is not because of me. Some of them posted provocations to me, but I didn't react like it is world war III. I have my own way of looking into things like everybody else, and I don't pretend that my universe of rules is the best out there - it is the best for me, if you don't like it just continue your way, and we may encounter on some different occasion. I am open-minded enough to listen and answer to people which seem to have a problem with me.Also, I do my thanks to all people that helped me, which is not what you see on every post on every forum.www.r-moth.com |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-28 : 10:09:44
|
| I read several of your posts, not just this topic. I saw several of your posts that I thought were offensive, and I saw several people take offense to them. Since the only common thing on these topics was you, I would say that you were the one being rude, despite your opinion of yourself.I can’t understand how you think it benefits you to be rude to people that are trying to help you. It just seems self defeating to me.CODO ERGO SUM |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-28 : 10:48:47
|
quote:
I read several of your posts, not just this topic. I saw several of your posts that I thought were offensive, and I saw several people take offense to them. Since the only common thing on these topics was you, I would say that you were the one being rude, despite your opinion of yourself.
So, you actually say that if zillion people listen "Backstreet Boys" they are the best musicians ever ?hm.... I must see where that fits in the equationquote:
I can’t understand how you think it benefits you to be rude to people that are trying to help you. It just seems self defeating to me.
And now you are lieing .... or to use more polite words, you are distorting the reality - I think you can read my thanks to every page I started. But, as you may see, I am not interested in trivial human emotions.Forget it Michael Valentine Jones, rise your own children.After all, this is not important - it is just life, universe and everything else....www.r-moth.com http://r-moth.deviantart.com |
|
|
|
jhermiz
3564 Posts |
Posted - 2006-03-28 : 11:15:49
|
quote:
Originally posted by majkinetor
quote:
I read several of your posts, not just this topic. I saw several of your posts that I thought were offensive, and I saw several people take offense to them. Since the only common thing on these topics was you, I would say that you were the one being rude, despite your opinion of yourself.
So, you actually say that if zillion people listen "Backstreet Boys" they are the best musicians ever ?hm.... I must see where that fits in the equationquote:
I can’t understand how you think it benefits you to be rude to people that are trying to help you. It just seems self defeating to me.
And now you are lieing .... or to use more polite words, you are distorting the reality - I think you can read my thanks to every page I started. But, as you may see, I am not interested in trivial human emotions.Forget it Michael Valentine Jones, rise your own children.After all, this is not important - it is just life, universe and everything else....www.r-moth.com http://r-moth.deviantart.com
Coming from someone who says quote:
"But I also understand due to my active nightlife on some other forums that there are other kind of people who have other reasons to post but to help. Those reasons may be various, and most of them are covered in basic sociology/psychology course. "
I'd say you hit home pretty well. MVJ he's not worth a dime, and I don't know what's worse, his English or his website...
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-28 : 17:14:22
|
| You are funny www.r-moth.com http://r-moth.deviantart.com |
|
|
|
|
Table variables as Function Parameters