how to create temporary table inside the store proc for getting result

Question

how to create temporary table inside the store proc for getting result

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
March 13, 2012 at 8:20 am

#269997

here iam having 2 store proc which will be execute in the 3 store proc
the first store proc1
create PROCEDURE [dbo].[CategoryName] @VALUES varchar(1000)
AS
SELECT @VALUES = RTRIM(@VALUES) + '%';
SELECT c.CategoryName ,CategoryId
--, SB.SubCategoryName,SB.SubCategoryId
FROM tblAdCategory c
--left join
--tblAdSubCategory SB on SB.CategoryId =c.CategoryId
WHERE c.CategoryName LIKE @VALUES;
if i execute this storeproc1
EXEC [dbo].[CategoryName]
@VALUES = 'e'
iam getting the result like this
CategoryName CategoryId
Education & Learning 3
Entertainment 8
Electronics & Technology 9
Events 12
the second store proc2
create PROCEDURE [dbo].[SubCategoryName] @VALUES varchar(1000)
AS
SELECT @VALUES = RTRIM(@VALUES) + '%';
SELECT SB.SubCategoryName,SB.SubCategoryId
FROM tblAdSubCategory SB
WHERE SubCategoryName LIKE @VALUES;
if i execute this storeproc2
EXEC [dbo].[SubCategoryName]
@VALUES = 'e'
iam getting the result like this
SubCategoryName SubCategoryId
Engineering jobs 77
Education & Teaching 83
Electronics 96
Event Organizers 119
EBAPX | Fax | Photocopiers 130
Events 164
Electricians 212
Education/Literati 243
Entertainment 244
Exhibitions - Trade Fairs 251
Entertainment 261
Engineering & CAD 281
ERP & CRM 282
Education Loans 317
here iam writing the main store proc
create PROCEDURE [dbo].[Name]
@VALUES varchar(1000)
AS
begin
EXEC [dbo].[SubCategoryName] @VALUES
EXEC [dbo].[CategoryName] @VALUES
create temp
(
CategoryName varchar(200)
CategoryId int
SubCategoryName varchar(200)
SubCategoryId int
)
select * from temp
end
if i execute this main store proc
[dbo].[Name] @VALUES='e'
i have to get out like this and iam trying for this only
CategoryName CategoryId SubCategoryName SubCategoryId
Education & Learning 3 Engineering jobs 77
Entertainment 8 Education & Teaching 83
Electronics & Technology 9 Electronics 96
Events 12 Event Organizers 119
EBAPX | Fax | Photocopiers 130
Events 164
Electricians 212
Education/Literati 243
Entertainment 244
Exhibitions - Trade Fairs 251
Entertainment 261
Engineering & CAD 281
ERP & CRM 282
Education Loans 317
can any one helpme how to make this

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Could you please edit your post. It's impossible to read...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please can you edit your post so that only the code is within the code blocks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Here is the original post with what looks like code inside code blocks. Unfortunately it doesn't make any more sense when formatted than it did originally.

here iam having 2 store proc which will be execute in the 3 store proc

the first store proc1

create PROCEDURE [dbo].[CategoryName] @VALUES varchar(1000)

AS

SELECT @VALUES = RTRIM(@VALUES) + '%';

SELECT c.CategoryName ,CategoryId

--, SB.SubCategoryName,SB.SubCategoryId

FROM tblAdCategory c

--left join

--tblAdSubCategory SB on SB.CategoryId =c.CategoryId

WHERE c.CategoryName LIKE @VALUES;

if i execute this storeproc1

EXEC [dbo].[CategoryName]

@VALUES = 'e'

iam getting the result like this

CategoryName CategoryId

Education & Learning 3

Entertainment 8

Electronics & Technology 9

Events 12

the second store proc2

create PROCEDURE [dbo].[SubCategoryName] @VALUES varchar(1000)

AS

SELECT @VALUES = RTRIM(@VALUES) + '%';

SELECT SB.SubCategoryName,SB.SubCategoryId

FROM tblAdSubCategory SB

WHERE SubCategoryName LIKE @VALUES;

if i execute this storeproc2

EXEC [dbo].[SubCategoryName]

@VALUES = 'e'

iam getting the result like this

SubCategoryName SubCategoryId

Engineering jobs 77

Education & Teaching 83

Electronics 96

Event Organizers 119

EBAPX | Fax | Photocopiers 130

Events 164

Electricians 212

Education/Literati 243

Entertainment 244

Exhibitions - Trade Fairs 251

Entertainment 261

Engineering & CAD 281

ERP & CRM 282

Education Loans 317

here iam writing the main store proc

create PROCEDURE [dbo].[Name]

@VALUES varchar(1000)

AS

begin

EXEC [dbo].[SubCategoryName] @VALUES

EXEC [dbo].[CategoryName] @VALUES

create temp

(

CategoryName varchar(200)

CategoryId int

SubCategoryName varchar(200)

SubCategoryId int

)

select * from temp

end

if i execute this main store proc

[dbo].[Name] @VALUES='e'

i have to get out like this and iam trying for this only

CategoryName CategoryId SubCategoryName SubCategoryId

Education & Learning 3 Engineering jobs 77

Entertainment 8 Education & Teaching 83

Electronics & Technology 9 Electronics 96

Events 12 Event Organizers 119

EBAPX | Fax | Photocopiers 130

Events 164

Electricians 212

Education/Literati 243

Entertainment 244

Exhibitions - Trade Fairs 251

Entertainment 261

Engineering & CAD 281

ERP & CRM 282

Education Loans 317

can any one helpme how to make this

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

The above is what the others were asking you to do. You edited is since they asked but it was still totally unreadable. I have made it readable but it still 100% unclear what you are trying to do. Please try to explain clearly what you are doing. Slow down when typing and make sure that what you are typing actually makes sense. It seems we have somewhat of a language barrier but I am sure we can work this out.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/