It’s been a while since I haven’t used SQL. Now that I’m using it on a daily basis at work, I may write a little more about SQL than before. So here is an article the aim of which is to show how to find the number of results returned by a stored procedure. It’s in fact a problem which can be easily solved.<\/p>\n
So let’s say we have a table containing animals names. Nothing fancy, just something like this:<\/p>\n
name\r\n----\r\nCat\r\nDog\r\nRabbit<\/pre>\nThat’s it for the table. Now we need a stored procedure, so let’s assume we have one : findNameLike<\/em> which take letters as parameter and returns animals names beginning with these letters.<\/p>\n
EXEC findNameLike @beginWith = N'C'<\/pre>\nCat.
\nYes, the above call will return Cat.<\/p>\nSo, if we want to know how much results were found, we will just write the following code:<\/p>\n
DECLARE @numberOfMatch INT\r\nEXEC findNameLike @beginWith = N'C'\r\nSELECT @numberOfMatch = @@ROWCOUNT<\/pre>\nThe number of results is now available in variable numberOfMatch<\/em>.<\/p>\n
That’s it.
\nBut maybe you also don’t want the stored procedure to return results when you call it. So we need to modify our code a little so that results will be stored in a temporary table and thus not returned, but rather stored in the table.<\/p>\nDECLARE @numberOfMatch INT\r\nDECLARE @tmpTable TABLE (\r\n\u00a0\u00a0 \u00a0name VARCHAR(25)\r\n)\r\n\r\nINSERT INTO @tmpTable \r\nEXEC findNameLike @beginWith = N'C'\r\nSELECT @numberOfMatch = @@ROWCOUNT<\/pre>\nI think we’re done here. In a few lines, we’re able to count the results from a stored procedure as well as suppressing its return.<\/p>\n","protected":false},"excerpt":{"rendered":"
It’s been a while since I haven’t used SQL. Now that I’m using it on a daily basis at work, I may write a little more about SQL than before. So here is an article the aim of which is to show how to find the number of results returned by a stored procedure. It’s … Continuer la lecture de « [SQL] Count results from stored procedure (without return) »<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[207,206,208],"class_list":["post-1730","post","type-post","status-publish","format-standard","hentry","category-code","tag-count","tag-sql","tag-stored-procedure"],"_links":{"self":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/1730","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1730"}],"version-history":[{"count":2,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/1730\/revisions"}],"predecessor-version":[{"id":1732,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/1730\/revisions\/1732"}],"wp:attachment":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1730"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1730"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1730"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}