You can combine your query script by program, and pass it as then parameter of the stored procedure, and execute it by keyword "EXEC"(Sample: exec('select * from mytable')) in the stored procedure. But you have to understand, when you called a stored procedure, a new session was created, and when you execute a script by keyword “EXEC” in stored procedure, another session was created for executing. So, if you want write any complex script in the stored procedure(for example, in stored procedure, you have to query a record set by a dynamic script, and do something others to the record set), you should use global temp table(table name start with ##) of SQL Server but not normal temp table(table name start with #), it can share to different session(you can access normal a temp table in same session only). So. In this case, I always write like following script:
Declare @cStatemtn nvarchar(max)
Declare @cFieldValue1 nvarchar(100)
Set @cStatement = 'select * from mytable into ##tmpTbl where field1 = ' + @cFieldValue1
if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl
Exec(@cStatement)
Select [ID], [Name], sum(score) from ##tmpTbl
Group by ID, [ID], [Name]
if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl